I am looking to create a macro that will edit the code of another macro that is already setup. Basically, I have a macro that will open and close every file that has data linked to this workbook. Because of the 255 character limitation of text in Excel, I have a simple macro to open/close each so it will bring over any additional characters past 255. Now, this macro is saved to a master file, and each month we do a save as, and start a new period on it. The files we open/close are named C:/data/RCC/FY2007/10543 - ifi/10543 CurrentMonth Rec.xls
Is there a way to make a macro, that will do a simple find -- replace in the VBA code of the original sheet, so that it looks for the right files. So, with the above example, I would replace "CurrentMonth" with whatever I enter into an inputbox (say AUG for August).
And if you have a moment, is there a way to make two input boxes, one to denote the Fiscal Year (in the file path it is the entry that is FY2007) and CurrentMonth, so that if it is the new fiscal month then it will create a new directory and save in that new location? So when it is FY 2008, I would do a save as C:/data/RCC/FY2008/10543 - ifi/10543 CurrentMonth Rec.xls?
I have an Excel worksheet used as an index for my holiday photo collection. The worksheet consists of a list of geographic locations visited for a particular holiday, and a hyperlink in each of those cells to the location of the corresponding photo on my hard drive ( eg C:NZ PhotosOtago\796.jpg). When I have taken some 10,000 photos on a trip, I find this an easy way to quickly view pictures of any site visited on the holiday.
The Excel index and the photos are stored in different partitions on my computer.
I now wish to share these photos by burning them to a DVD, but I would like the Excel photo index to be also on this disk, but with hyperlinks that refer to the photo files on the DVD, not the originals on my hard drive.
If this is possible, and the format of the hyperlink that I should use in future? e.g., instead of making the hyperlink E:NZ PhotosOtago\796.jpg, could I make it ..NZ PhotosOtago\796.jpg, so that I can transfer the Excel spreadsheet to a DVD with the photos?
Also, is there a way to do a global change of the hyperlinks in all the worksheet cells?
I would like to place a command in a macro to have a workbook replicate itself WITHOUT replicating its macros. Is that possible? Is there a command? I am using Excel 2003.
I have created a workbook ( with protected sheets) containing two macros " To Insert a Row ( Copying the formula from the row above) and "Delete a Row", both, assigned to a customized toolbar.
when I move the workbook, say from the folder where I created it originally to the desktop, the macros stop working giving error like THE MACRO " C:so n so... CAN NOT BE FOUND!
I have created a spreadsheet to do all payroll calculations. Would like to print out each individual persons detail to give them a "receipt" of gross pay and deductions.
How I could take a sheet with 100 names and print out each line.
i have the table of holiday days with 7 columns(7 countries).Each column consists of 10(or more rows (holiday days).
I have a formula for calculating start day for a deposit which has to be renewed. formula is =workdays(end date of deposit,-2,A1:A10). -2 in formula is because all the trades in the market are traded Today+2days. A1:A10 is column of holiday days. This formula calculates the start of deposit excluding weekends,holiday days.
BUT I have more then 7 countries and I would like to have a macro so when in cell F10 I type SWEDEN,my formula automatically use the column with holiday days B1:B10 for sweden. When I type Finland, the formula uses column C1:C10 with holiday days from Finland respectively.
I work at a trading firm and use pivot tables to report on the success of traders on a daily basis. I add daily trading data to a raw data table that powers a set of reports. In one report I want to view MTD stats for a filtered group of 10 traders. The issue is that if I add a set of daily data that includes a new trader name, it will automatically be pre-checked and added to this report (and this happens daily). The only solution I came up with is to add another column in the raw data table that would allow me to group these traders and then use a page filter to include only them. This will work but I'd rather avoid adding columns to an already unruly data table (and would like flexibility to periodically define and track an arbitrary set of traders).
I created a VBA module (Excel 2003) that contains many generic functions that I wrote. I want to use the functions from that modules in other Excel VBA projects (something like "Include" a library of functions).
I'm trying to write a formatting macro that will insert a gray divider (row) into a blank line. I'm going to do this with a keyboard command, but I'm having a hard time selecting to column AJ. Obviously this command...
Range(Selection, Selection.End(xlToRight)).Select
...takes you to the end of the page, but I don't need to go that far, just to column AJ.
The workbook has a tab for each day, and there are variance columns. The tabs are named for the date, like 1.20, 1.21, 1.22, etc.
For example, the formula on 1.21 in D2 is simply =C2-'1.20'!C2
That works fine, but it's a time-consuming job to always change it. You have one sheet to copy many times for all the other formulas, but then have to re-name and change that variance formula on each worksheet.
Is there a way to reference the prior worksheet without using it's specific name? =C2-'prior worksheet'!C2 would be nice...I could take my one sheet and copy it about 23 times for each workday, then copy that whole workbook to use in the subsequent months without making any formula changes.
Is there a way to generically sort all pivot rows of a pivot table alphabetically each time the table is refreshed? I have a pivot table that pulls its data from a dynamic named range on a separate worksheet. As users add data to the range and refresh the pivot table the newly added items appear at the bottom of the table rather than alphabetically. I know how to specifically call out a pivot row and have it sort alphabetically (see code below) but I still want to allow users the flexibility of changing the fields and order of the pivot rows so specifically calling out a pivot row by field name isn’t going to work. I need to somehow add code that says: For each pivot row (1 to n, where n = number of pivot rows) sort each in alphabetical order.
My problem today, is actually being able to call the routines. I have tried to make a generic setup, so that each subroutine to be called is named "Macro_[number here]". So Macro_1, Macro_2 etc. By doing this I am hoping to be able to call all the subroutines using a For Each code.
How is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.
Right now there are around 600 customers in this list.
I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.
I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
I need to figure a way to make to cells with dates equal each other if the day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)... For k = 1 To numrows Selection.EntireRow.Insert Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1 If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then Rows(l & ":" & l).Select Selection.Delete Shift:=xlUp edelrows = edelrows + 1 End If Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
