Worksheet tabs in a Macro

[jstoner50]

I have a macro that references the sheet tab name that I have given it
The problem is these Tab names change periodically, And that means
have to run a find and replace function to change my Macro with thes
tab names with the new names. When I look at the module in the Macr
screen they show sheet1=(my Name). But what i need is to reference m
macro to the Excel default name sheet1, sheet2, sheet3,...etc. I hop
I explained my issue.

Thanks for any hel

 

[Don Guillett]

try sheet7.etc

 

[John Michl]

Have you considered using the sheet index instead of the name? Sheet1
is always an index of 1 even though the name changes. I'd probably DIM
the sheets at the beginning of the code. For instance, assume Sheet1
was renamed to "Info", Sheet2 to "Data" and Sheet3 "Results". Then....

Sub SheetNames()
Dim shInfo, shData, shResults As Worksheet

Set shInfo = Sheets(1)
Set shData = Sheets(2)
Set shResults = Sheets(3)

i = 1
For Each sh In Sheets
MsgBox Sheets(i).Name
i = i + 1
Next
End Sub


HTH - John
www.JohnMichl.com

 

[jstoner50]

The problem is that Excel looks at the name of the tab you have given
it. I'm trying to use Excels default name when you first start out
Excel creates a sheet1 once you have changed the name Excel does not
recognize sheet1 again. I hope this clears up the confusion.

Jim

 

[John Michl]

Jim, my method uses the index for the sheet which you cannot change.
Sheet1 always has an index of 1 even when you change the name to
something other than Sheet1.

- John