A macro that will work on different files?

gli

Board Regular
Joined
Jun 8, 2006
Messages
92
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?
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,910
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.
 

gli

Board Regular
Joined
Jun 8, 2006
Messages
92
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,910

ADVERTISEMENT

ActiveWorkbook.Worksheets("County 1") can be replaced by ActiveSheet.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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
 

gli

Board Regular
Joined
Jun 8, 2006
Messages
92
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,550
Members
414,155
Latest member
Grainne whiteside

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
Top