Get macro to run on any worksheet without renaming the sheet?

A

Andrew B

I'm trying to get a macro I've designed to run on any worksheet.

However, when I start the macro it errors and needs to be on a worksheet
that has the same name as the one I designed it on.

Is there any way to get the macro to run on a worksheet, regartdless of what
the name is?
 
B

bpeltzer

Absolutely, it's possible. However, the ease of doing so depends heavily on
the specifics, such as how much worksheet switching did you record and
whether you've got formulas that reference other worksheets. Let's try the
simplest approach first. Open the workbook containing the macro and switch
to the VB Editor (alt + F11).
In the Project Explorer (probably at the top left; if it's not visible, View
Project Explorer), double click on Modules if necessary so that Module1 is
Click to expand...
visible. Click on Module1 (or whichever module contains your macro). Then
seach the macro for a statement like:
Sheets("Sheet2").Select
Is there only one like that? If so, just comment it out by adding an
apostrophe before the statement:
'Sheets("Sheet2").Select
If there are multiple lines like that, with different sheets selected, we'll
have a little more work to do. And if there are formulas that reference the
worksheet (ex: =sum(Sheet2!A1), more work still.
If this simple approach doesn't cut it, please provide more detail,
especially about which sheet(s) you're switching between and what formulas in
the macro contain worksheet references.
 
A

Andrew B

I'm afraid the simple approach wasn't enough!

There are numerous references to the original sheet, mainly for switching to
copy data from the original sheet to a new sheet.

Basically, if I could get the macro to rename any sheet I apply it to to
"data" then the rest of the macro would run okay - if thats possible.

Thanks

Andrew
 
D

Dave Peterson

Don't refer to the worksheet by name.

You can use activesheet instead.

activesheet.range("a1").value = "hi there"

worksheets("sheet9999").range("a1:b99").copy _
destination:=activesheet.range("C1")
 
B

bpeltzer

Then I'd create a variable to store the active sheet when the macro is
invoked, then change the select statements to use the variable rather than a
fixed name.
Right after the Sub line, and before any other statements add:
Dim StartSheet As String
StartSheet = ActiveSheet.Name
Then change lines such as
Sheets("Sheet2").Select
to read
Sheets(StartSheet).Select
Note that you only want to make this substitution when your starting sheet
is being selected, NOT when other sheets are selected. Also, I've kind of
assumed that whatever OTHER sheets your macro is using will exist in the
workbook, because those sheets' names are hard-coded in your macro as well.
--Bruce
 
D

Dave Peterson

Or even just use a variable to represent that worksheet:

Dim ActWks as worksheet
set ActWks = activesheet
....
'activate the workbook, just in case a different workbook is active.
actwks.parent.activate
'select the worksheet
actwks.select
 
Top