A macro that will work on different files?

gli

Board Regular
Joined
Jun 8, 2006
Messages
96
Since I upgraded to Excel 2007 I can't get macros I record to work in any other workbook other than the one they are created in. I can see from looking in the code that the tab name is part of the macro. I don't know enough about VBA to be able to work around this. I need to be able to run the same macro on many separate yet almost identical files. Each one is a database that shows the same information from file to file. Example, each file is always set up the same way, fields in the same order, the exact same type of information is presented in each one. The only reason they are different files is because they cover different counties, so the account list is different in each one. I need to format these files all the same way but am currently unable to do so without recording a macro for each file. There are too many counties in my state for this to be a viable option. I'm looking to be able to use one macro across the board in all of these different files. Can this be done? Perhaps utilizing a form of wildcard?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Save the file with the macros as XL ADDIN (*.xla) to the following location: %userprofile%\Application Data\Microsoft\AddIns
Then go to: Excel Options -> Add-ins -> Manage: Excel Add-ins > Go...
in the next dialog enable (check) the name of the file you just saved to enable it.
Then just use the name of the macros you need - you just have to know them.
 
Upvote 0
If replace Workbooks("Workbook1.xlsm") with either ThisWorkbook or ActiveWorkbook, the macro will work on books other than Workbook1.

ThisWorkbook refers to the workbook in which the macro resides.
ActiveWorkbook refers to the workbook that is active when the macro is run.

It sounds like what you might want to do is.
Record a macro, indicating (at the beginning) that it is to be stored in the Personal Macro Workbook.
Edit the Macro, replacing the references to a specific workbook (e.g. Workbooks("Workbook1.xlsx")) with ActiveWorkbook.

Then, since the Personal Macro Workbook is always avaliable, you can call the macro at any time, but have it act on whichever workbook is active when you press RUN.

If your macro involves between workbook actions, you'll have to learn VBA. But if your desired actions are all within one workbook, this approach might be easiest for you, without the downtime to learn VBA.

One suggestion, if your macro changes a workbook from Bad to Good, try to devise your actions so that when the macro is run on a Good workbook, the workbook remains Good.
 
Upvote 0
My macro already contains ActiveWorkbook in the code, so I tried a couple of different things and they didn't work. Here is an example of what the code looks like - it is tied to the name assigned to the worksheet:

ActiveWorkbook.Worksheets("County 1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("County 1").Sort.SortFields.Add Key:= _
Range("D2:D6344"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("County 1").Sort.SortFields.Add Key:= _
Range("E2:E6344"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal


The workbook's name is County 1, and the one worksheet contained in it is named the same. However, each file is named after a different county and each contains one worksheet with the same name. Ex: County 2, County 3, etc.
 
Upvote 0
is the macro a User Defined Function or a SUB ?
Did U try to use XLA (my post above)- it is the clean way with no additional complications.
if you have UDFs you can add them to the Functions Wizard with:
Application.MacroOptions "MacroName", "Description",,,,,"Category"

if (not a private) SUB - just call it by name or assign it to a button
 
Upvote 0
ActiveSheet did the trick! *happy dance* Thank you!

Bobsan, your option was a little more involved as far as my skills go, so I was keeping it in my back pocket in case the other option didn't work. I do appreciate your contribution though. Thanks so much! :)
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top