TAB names not changing in Macro

[sb003848]

Hello,

I've created a macro that does many different things in different TABs in an Excel document.

The problem is that I would like, from time to time, to change some of the tab names. The problem is that when I do that, the macro doesn't work anymore...

Any suggestions on how this can be fixed???

For example, is there a way to tell my macro to go to the 18th tab instead of giving it the tab name???

 

[texasalynn]

Would be helpful if you post your code

 

[sb003848]

This is only part of my macro but you get the idea...

Windows("<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:State w:st="on">TV stations.</st1:State></st1lace>xls").Activate<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Sheets("FOX").Select<o></o>
Range("P11:BW1011").Select<o></o>
Selection.Copy<o></o>
Windows("Promotions.xls").Activate<o></o>
Sheets("FOX").Select<o></o>
Range("P11").Select<o></o>
ActiveSheet.Paste<o></o>
<o></o>

 

[texasalynn]

maybe something like this

Sub selecttab()
Dim ws As String
ws = Worksheets(8).Name
MsgBox "sheet #8 name is " & ws

End Sub

 

[sb003848]

Unless I'm not reading your code correctly but I don't need to have the name of the tab in a kessage box but changed in my macro if changed in the workdocument.

 

[lenze]

Ther are 3 ways to refer to a sheet in VBA
1) The Assigned Name--The name you see on the Sheet tab
2) The Code Name-- The Name assigned by the VBE
3) The Index Number- The Position of the sheet in the Workbook
The Assigned Name and Index Number can change. The Code Name can not. If you open the VBE(ALT+F11), you will see in the project explorer something like this

Sheet2("somename")

"somename" would be the Assigned Name
Sheet2 would be the Code Name
And the Index Number would be the position of the sheet in the Workbook. In code, you refer to these like this

Sheets("somename").Range(...   'Assigned Name
Sheet2.Range(....   'Code Name
Sheets(2).Range(...   'Index Number

To see how this works, try this experiment
Open a new workbook with 3 or more sheets
Rename Sheet1 to "Test"
Now Rename Sheet2 to Sheet1
Now move sheet2(Named Sheet1) after Sheet3. Now open the VBE and insert a module. Enter this code

Sub IdentifyMe()
Range("$A$1")= "My Assigned Name is "& ActiveSheet.Name
Range("$A$3")="My Code Name is "& ActiveSheet.CodeName
Range("$A$5")="My Index Number is " & ActiveSheet.Index
End Sub

Now close the VBE(ALT+Q) and, with the original Sheet2 active, run the code
you will see
Assigned Name --Sheet1
Code Name -- Sheet2
Index Number --3
What this means, is if you use the Assigned name or the Index Number in code, you can not rename your sheet or change its position in the workbook!!
HTH
lenze

 

[sb003848]

The reason why I'm asking this question is due to the fact that I have one document with many tabs in which I'll create about 25 different macros.

Then, I'll do a SAVE AS to save it for a different market which will make me change the names of all tabs in the new file. I'm trying to find a way to make my life easier than by having to go in each of the 25 macros and changing the names of about 50 tabs...

Thanks for letting me know if there an easy way to do this in my macros!!!

 

[texasalynn]

so if Peter's code not working for you? We are going to need your help in your trying some of these to see where the problem lies.

 

[lenze]

Did you not read my post? In your macros, use the CODE NAME (or INDEX NUMBER?) for each sheet.

lenze