Worksheet tabs in a Macro
 

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 I
have to run a find and replace function to change my Macro with these
tab names with the new names. When I look at the module in the Macro
screen they show sheet1=(my Name). But what i need is to reference my
macro to the Excel default name sheet1, sheet2, sheet3,...etc. I hope
I explained my issue.

Thanks for any help


--
jstoner50
------------------------------------------------------------------------
jstoner50's Profile: http://www.excelforum.com/member.php...o&userid=28651
View this thread: http://www.excelforum.com/showthread...hreadid=483154

Worksheet tabs in a Macro
 

try sheet7.etc

--
Don Guillett
SalesAid Software

"jstoner50" wrote
in message ...

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 I
have to run a find and replace function to change my Macro with these
tab names with the new names. When I look at the module in the Macro
screen they show sheet1=(my Name). But what i need is to reference my
macro to the Excel default name sheet1, sheet2, sheet3,...etc. I hope
I explained my issue.

Thanks for any help


--
jstoner50
------------------------------------------------------------------------
jstoner50's Profile:
http://www.excelforum.com/member.php...o&userid=28651
View this thread: http://www.excelforum.com/showthread...hreadid=483154

Worksheet tabs in a Macro
 

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

Worksheet tabs in a Macro
 

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


--
jstoner50
------------------------------------------------------------------------
jstoner50's Profile: http://www.excelforum.com/member.php...o&userid=28651
View this thread: http://www.excelforum.com/showthread...hreadid=483154

Worksheet tabs in a Macro
 

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