I have a macros in my spreadsheet when I open it a number in the macros goes up 1 each time the worksheet is opened. I want to put this file on a server that can be accessed by various people. The problem I am getting is every time someone opens it from a different location with a different PC the number starts at 1 again. I want to keep the number going up 1 each time no matter who opens it.
View 4 Replies
I'm trying to open a file from server.After doing all the changes I have to save the file back to the server in the same name. It is asking for save as option.it shouldn't. I want to save the file in the same path and in same name without asking the option of "Save as". I hv full permission for editing the file
View 4 Replies View RelatedI have a macro which saves file to a specific folder. Problem is I can save the file only in my local drive C: but not on a server for some reason.
Here is the
At home in my home computer this code worked when C: was selected.
ChDir ("c:")
Title = Application.GetSaveAsFilename(Name, "Excel files (*.xls), *.xls")
ActiveWorkbook.SaveAs Name
Save the file in specific place
But when I substituted C: path with my server path it doesnt work! Have a look. Its the exact same code except my path is different.
ChDir ("\Lnf001Lnf1vol1SharedOP_ENGWork Order")
Title = Application.GetSaveAsFilename(Name, "Excel files (*.xls), *.xls")
ActiveWorkbook.SaveAs Name
I have a spreadsheet which I used in a previous job, which was set up by our old IT Department. Basically, it allows me to save information onto it, which I can then retrieve, (in any conditions/search fields i.e. in a date range etc) but the problem is; when I try to use it on my laptop, I look at the editor used for macros, I can see that the spreadsheet is linked to the servers of my old company. Therefore, I cannot use it myself
I do not know a lot about macros etc. but I can send a copy of the spreadsheet to anybody who is willing to help!
I think it is just a case of changing the macros/strings of where to draw and save information to/from, but I don;t know.
Is it possible to compare two columns side by side to highlight/change color of difference between the two cells
I need to compare and show the changes to the record from previous version and generate a report which will highlight the changes (Rather than just saying that record has changed It should tell what has changed)
For example if the
Column 1 (Previous Version) :The structure contains Wire, Bolt, Stmaps etc
Column 2 (Current Version) : The structure contains Wire, Bolt, Stamps etc
Data Changed: YES
In report is should show two columns side by side and in Column 2 it should highlight "Stamps"
Is there a way to have separate Report Filters for each column in a pivot table?
I am trying to create a pivot table with 3 columns - 1: account code 2.current year (2011) 3. prior year (2010)
My Row Label is an account code field - each row is a different account code that sums up dollar amounts for the two financial years. The problem is that I need Columns 2 and 3 to have separate Report Filter criteria. So in Column 2 - I need the filter to weed out anything that is not 'Financial Year 2011'. In Column 3, I need the filter to weed out anything that is not 'Financial Year 2010'. Setting up a filter for one screws up the second, and vice versa.
Alternatively, how can I make two side-side-pivot tables that each have their own Report Filters?
I have two sheets that need to have unique partial matches aligned side by side. My first sheet is my database. The righter most column of this sheet contains a unique combination of letters and numbers.
My second sheet contains a single column which I export each month from our CMS and is a list of URLs. Within each of these URLs appears the unique set of letters and numbers from sheet 1 (and only appears once).
There are about thrice as many URLs in sheet 2 as there are entries in my sheet 1 database.
Ideally, I would like the URL from sheet 2 that contains the unique combination from sheet 1 to be pasted in the cell immediately to the right of the given unique combination.
However, I will settle for a list of trues and falses next to the URLs in sheet two that I can erase the falses, sort and paste next to the sorted list from sheet 1.
i have 2 excel worksheets. One of them contains over 20,000 rows of entries (old side) and the other contains about 900 rows of entries (new side). Both contain the same 9 column headings. I've shortened the list in my workbook example.
What i need to do is excel to delete any row on the old side that is not contained on the new side. I don't need the rows to match fully but the column A's (Doc number) are what i want it to check against. I've included a summarised version of the database and the old and new are separated by a heavy blue line. Need to be left with the same rows on either side of that blue line at the end.
I'm trying to figure out how some conditional formating and I cant get it to work.
What I want is for column H to be highlighted based on the value of column I.
So... If I2<721, then H2 will highlight green. If I2 is between 721&780, then H2 will be highlighted yellow. And if I2>780, then H2 will be red.
I know how to do it one cell at a time, but I need it to apply to the entire column based on the cell next to it.
Also, I dont have access to visual basic.
I need function that swaps text from side to side. Text at the left of "x" should be placed at the right side of the "x" and from right to left side:
6x15 -> 15x6 plaah plaah xsomething -> somethingxplaah plaah How to do this?
I have a sheet with a number of repeating blocks copied down.
I would like a macro that when I select the first block, say range "A1:D30", the macro will cut the blocks below and put them side by side, so that I can compare them across.
i.e cut "A31:D60" and put it in "F1:I30" and so on ...
I have created a spreadsheet that has information about networks, subnets etc..
The top header for both companies in (CSV format) reads:
Network Namenet_addr1net_addr2net_addr3mask_lengthorg_idcidrlast_upd_dt
I basically split the spreadsheet to show both side by side.
I have this information for both networks that I am trying to compare what subnets, names, IP address etc... over-lap.
We are merging two companies into one, and I need to identify or highlight what information on both companies over-lap.
why a person such as myself cannot have two or more Excel Docs on the screen side by side at the same time? And if it can be done, how do you do it?
View 9 Replies View RelatedI'd like to be able to just open the spreadsheet up and have the macros already saved in there. Is there a way to save these macros?
View 2 Replies View Relatedhow to save my macros to a spread sheet? It seems every time I close my sheet(s) (after saving???) and come back to them later, my macros are gone??
The form is completed by the project leader, then they hit a Command Button and it's sent to the Senior Manager. After the Senior Manager looks at it and approves it, he then sends it on to Admin (me!) so the project can get set up on all of our systems.
We recently upgraded to Office 2007 (yes, we are a bit late!) and now the Macro that sends the form to Admin no longer works. An example of the error is as follows:
Cannot run the Macro "FY06-10 PROJECTS 19-Jan-10 08-32'EmailtoAdmin.EmailtoAdmin'. The macro may not be available in this workbook or all Macros may be disabled.
Here is the code that I use to send it to the Senior Manager:
Sub MailtoSM()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
With Destwb
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else.........
Whenever I save and close the attached, after selecting the NO on line 23, I lose all the buttons associated with my macros. If I save with everything expanded, no rows hidden, my buttons stay put.
View 1 Replies View RelatedHow/ where to save macros. If I save a macro "normally" ("in this workbook") then it only applies to that one file, right? And I can't use it in any new files.
So what do I do if I want a macro I can use in "all" my Excel files?
And can I email a file containing a macro to a colleague, so he can work on the file, using the macro too?
I ask, because I've had trouble with this in the past (tho in MS Word) where I couldn't get the macro to be emailed along with the file...
Also - how can I get an .xls file with a macro to work in newer versions of Excel? And can I use Excel 2010 to save a file with a macro in an .xls format, so it can be opened in older Excel versions too?
So i have a macro that i wrote that willclean up these datafeeds that i get. i want to be able to use them for each spreadsheet. So the first bit i tried was saving a personal.xls file in the xlstart folder in XP (MSE 2003) . That would automatically pull up but when i tried to run the macro on other worksheets i get the generic 400 error.
the next thing i tried was copying the macro to a module and adding function tags and taking out the sub tags. i then "saved" that in the addins folder then tried assiging my custom button to is through the macros prompt. When i gave the title box the absolute path to the addins folder with clean.xla cited it said it was invalid, so i tried saving it just as clean.xla expecting it to locate the file there anyway.
this far each time i try to run the macro globally it says it cant find the current sheetname!macroname So i must be missing a step somewhere... can anyone give advice, i have searched the forum and couldnt find a similar problem for solutions.
on the attached workbook i have date on the left jand side and some info from this data will be produced on the right hand side.
Can i have the worksheet so that if the user scrolls down the data on the left the info on the right is still in view?
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 71 bytes) in /home/eforum/public_html/search.php on line 1155. I have following macro, what worked just fine in 2003. Now I'm working with 2007 and I can't set up the Microsoft Visual Basic for Applications Extensibility library. Here's the
View 2 Replies View Relatedi have a sheet that contains side-by-side,below two formatted colums which has continuous weeks appearance(named PE sheet). i wanna to rearrange the colums to be a single top-down colums according to continuous weeks(named sheet2.are there any easier and faster techniques?a macro probably or a function? arrange(english).xlsm
View 1 Replies View RelatedI have created a form wherein i have created a textbox to enter the excel filename and three different combobox to select the year, month and the day respectively. These are actually the folders on my hard drive and the excel files which i want to open is inside these folders with similar names. I want to search these files for the selected year, month and day from the combobox. The filename will be provided in the textbox. the path for the files is say c:yearmonthday*.xls
View 6 Replies View RelatedI would like to, using VBA, assign a hyperlink to an excel cell that links to another worksheet within the same workbook. I've tried the forums, and combined with a previous post and MS Excel Help File, I've come up with the following attempt:
With Worksheets("Test Destination")
.Hyperlinks.Add Anchor:=.Cells(counter + 4, 5), Address:=strHyper2, _
TextToDisplay:=(#1/1/2007# + counter)
End With
strHyper2 is previously defined: strHyper2 = "[WeatherTester.xls]1!A1"
My spreadsheet file is "WeatherTester.xls", my worksheet is "1", and "A1" is the desired link location for the cursor. Counter is an integer. I get the error "Invalid procedure call or argument". I also tried to enter a hyperlink directly through the formula property, but Excel didn't like the single quotations I had to use within the HYPERLINK function. The hyperlink formula works in the following form: =HYPERLINK("[WeatherTester.xls]1!A1","1 Jan")
Our state carries a 4% sales tax on all items except food and prescriptions.
Our county carries a 3% sales tax on everything.
Attached on my work sheet:
Column "C" determines if an item is either food or non-food.
"G5" is the subtotal of column G
"G4" is the S/tx on "G5" at 3%
"G3" is the S/tx on "G5" at 4%.
"G2" is the gross pay out.
My question is:
I'd like a formula for Cells "G3" and "G4" that can determine which items paid for in column "G" match a "N" or an "NF" in column "C".
If an item in column "G" represents a "F" in column "C", then there should not be anything in cell "G4" If an item in column "G" represents a "NF" in column "C", then there should be a figure in "G3" & "G4".
I need to know how to read a filename from my local drive c: ? for example in my local drive i have some 100 files, i need to pick there filename, date and time stamp .. etc.
View 7 Replies View RelatedI am working on a process model, which requires that the same basic procedure be carried out for each unit in the process.
One worksheet is allocated to each unit with data concerning the flow streams being passed from the previous unit and the product being linked to the next unit. Although the same procedure is therefore being followed on each sheet, the input data differs.
I currently have the code to perform the required technical operations and calculations for a single unit. This code is very much dependent on named ranges.
I want to use the same core to perform the routine-operations, but need either unique named ranges on each page or I need a way to define name that is limited to the specific worksheet on which it is located.
For example, I might have an input range for a heater and splitter, with a technical add-in in VBA performing calculations on the input for each unit. How can I use two separate instances of the name "Input" instead of "Input_Heater" and "Input_Splitter"?
The default program for opening JPG files is "Windows picture and fax viewer" which is fine with one of my colleagues. Upon declaring and clicking on a Hyperlink, to a JPG fle, the picture is opened in "Microsoft photo editor". How can one define the program in which Pictures opens via Hyperlinks will be displayed!? (WINXP & Office 2002)
View 3 Replies View Related30 people in different countries use a sophisticated VBA Excel file on their local PCs, let's call it myexcelfile.xls. This file contains no custom data, as all data is stored in myexcelfile_backup.xls.
The biggest challenge with this set up is that whenever there is a newer release or bugfix of myexcelfile.xls, I need to send it out per email to 30 people and hope they replace it with my updated version.
Note that newer versions can replace older versions without any data loss, as all data is stored in the backup file.
Idea: with tools like WGET or BITSADMIN, I was thinking of creating a batch file, that would simply download the latest version from a FTP server, and replace the older version with the newly downloaded one.
Constraints: I shouldn't be using any software that is not native to Windows 7the files can sit in different folders on these 30 computers, therefore the update mechanism would have to identify the path they are in.the update mechanism would have to close the file first before running the update. Question: What is the best way to go about this? A DOS Batch file? Or another excel file that is just the "updater", meaning, it's only purpose is to download the new file from the web and overwrite the old file?
I have Excel 2003 SP3 on Vista Home Basic fully patched. My code runs very slowly until I pull out the ethernet cable then it runs quickly.
View 9 Replies View Related
