Excel Worksheet Code Name vs WS Tab name in Array

[Randy11]

Am looking for some assistance with how to refer to the WS (Worksheet)code name versus WS tab name used when a macro is recorded. Am looking for a solution that is not affected the the WS Tab name being changed or the position of the WS tab in the workbook. In the recorded macro below if one of the worksheets is renamed & the code is run, this would cause a Run Time error as it would no longer recognize the worksheet. There must be a different way to refer to the worksheets in the array.
Assistance Appreciated

Sub Test_Multisheet_Macro()
'
' Test_Multisheet_Macro Macro
'

'
Sheets("Sheet250").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", _
"Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13", "Sheet14", "Sheet15", _
"Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23" _
, "Sheet24", "Sheet250")).Select
Sheets("Sheet250").Activate
Sheets(Array("Sheet25", "Sheet26", "Sheet27", "Sheet28", "Sheet29", "Sheet30", _
"Sheet31", "Sheet32", "Sheet33", "Sheet34", "Sheet35", "Sheet36", "Sheet37", "Sheet38" _
, "Sheet39", "Sheet40", "Sheet41", "Sheet42", "Sheet43", "Sheet44", "Sheet45", _
"Sheet46", "Sheet47", "Sheet48", "Sheet49")).Select Replace:=False
Sheets(Array("Sheet50", "Sheet51", "Sheet52", "Sheet53", "Sheet54", "Sheet55", _
"Sheet56", "Sheet57", "Sheet58", "Sheet59", "Sheet60", "Sheet61", "Sheet62", "Sheet63" _
, "Sheet64", "Sheet65", "Sheet66", "Sheet67", "Sheet68", "Sheet69", "Sheet70", _
"Sheet71", "Sheet72", "Sheet73", "Sheet74")).Select Replace:=False
Sheets(Array("Sheet75", "Sheet76", "Sheet77", "Sheet78", "Sheet79", "Sheet80", _
"Sheet81", "Sheet82", "Sheet83", "Sheet84", "Sheet85", "Sheet86", "Sheet87", "Sheet88" _
, "Sheet89", "Sheet90", "Sheet91", "Sheet92", "Sheet93", "Sheet94", "Sheet95", _
"Sheet96", "Sheet97", "Sheet98", "Sheet99")).Select Replace:=False
Sheets(Array("Sheet100", "Sheet101", "Sheet102", "Sheet103", "Sheet104", "Sheet105", _
"Sheet106", "Sheet107", "Sheet108", "Sheet109", "Sheet110", "Sheet111", "Sheet112", _
"Sheet113", "Sheet114", "Sheet115", "Sheet116", "Sheet117", "Sheet118", "Sheet119", _
"Sheet120", "Sheet121", "Sheet122", "Sheet123", "Sheet124")).Select Replace:= _
False
Sheets(Array("Sheet125", "Sheet126", "Sheet127", "Sheet128", "Sheet129", "Sheet130", _
"Sheet131", "Sheet132", "Sheet133", "Sheet134", "Sheet135", "Sheet136", "Sheet137", _
"Sheet138", "Sheet139", "Sheet140", "Sheet141", "Sheet142", "Sheet143", "Sheet144", _
"Sheet145", "Sheet146", "Sheet147", "Sheet148", "Sheet149")).Select Replace:= _
False
Sheets(Array("Sheet150", "Sheet151", "Sheet152", "Sheet153", "Sheet154", "Sheet155", _
"Sheet156", "Sheet157", "Sheet158", "Sheet159", "Sheet160", "Sheet161", "Sheet162", _
"Sheet163", "Sheet164", "Sheet165", "Sheet166", "Sheet167", "Sheet168", "Sheet169", _
"Sheet170", "Sheet171", "Sheet172", "Sheet173", "Sheet174")).Select Replace:= _
False
Sheets(Array("Sheet175", "Sheet176", "Sheet177", "Sheet178", "Sheet179", "Sheet180", _
"Sheet181", "Sheet182", "Sheet183", "Sheet184", "Sheet185", "Sheet186", "Sheet187", _
"Sheet188", "Sheet189", "Sheet190", "Sheet191", "Sheet192", "Sheet193", "Sheet194", _
"Sheet195", "Sheet196", "Sheet197", "Sheet198", "Sheet199")).Select Replace:= _
False
Sheets(Array("Sheet200", "Sheet201", "Sheet202", "Sheet203", "Sheet204", "Sheet205", _
"Sheet206", "Sheet207", "Sheet208", "Sheet209", "Sheet210", "Sheet211", "Sheet212", _
"Sheet213", "Sheet214", "Sheet215", "Sheet216", "Sheet217", "Sheet218", "Sheet219", _
"Sheet220", "Sheet221", "Sheet222", "Sheet223", "Sheet224")).Select Replace:= _
False
Sheets(Array("Sheet225", "Sheet226", "Sheet227", "Sheet228", "Sheet229", "Sheet230", _
"Sheet231", "Sheet232", "Sheet233", "Sheet234", "Sheet235", "Sheet236", "Sheet237", _
"Sheet238", "Sheet239", "Sheet240", "Sheet241", "Sheet242", "Sheet243", "Sheet244", _
"Sheet245", "Sheet246", "Sheet247", "Sheet248", "Sheet249")).Select Replace:= _
False
Range("A3:E7").Select
Selection.ClearContents
Range("A11:E15").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A18").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet251").Select
Range("B5").Select
End Sub

 

[UnsolvedCoding]

If you are looking to change the way the macro is recorded I don't know that you can do what you are trying in the way you describe.

To change the names in code its simply Worksheetname = TabName.

I am going to look for a post that may help you. Back in a sec.

 

[UnsolvedCoding]

Look this thread over, it may give you some useful info.

http://tek-tips.com/viewthread.cfm?qid=1682564

It deals with sorting worksheets, but if you can create a list of worksheet names perhaps you could use parts of it for that purpose.

 

[Randy11]

Please explain, do not understand what is meant by "To change the names in code its simply Worksheetname = TabName."

Can you provide a sample of how Sheet1 would be referred to if the following were the case. Sheet1's tab name in the workbook has been changed to "Bananas"

How can I refer to this worksheet "Bananas" that has a code name in the background as Sheet1

In VBA under Microsoft Excel Objects the worksheet shows as follows: Sheet1 (Bananas)

 

[xlbo]

The simple answer is that if you wan the code to work even if tab names are changed you need to use the code name for the sheet

Maybe it is better for you to tell us what you hope to achive with the code in a business sense and we can help you implement that in a more robust way

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

You've already got the answer in thread707-1674631.


combo

 

[SkipVought]

Your question is like you asking your mechanic, how to store bottles of air in your trunk, because you want to keep your tires inflated.

Please tell us WHAT it is that you need to accomplish, rather than HOW you think it ought to be accomplished via VBA code.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Thanks for the friendly banter guys.

Simply put, would like to be able to change the displayed tab name on the worksheets without causing a VBA error. So far have not been able to accomplish this. An example would be helpful.

Thanks & Regards

 

[SkipVought]

I routinely change each sheet CodeName and only refer to CodeNames in my VBA code.

I change the CodeName as a design-time activity for any sheets that my code will reference. For run-time activities, like ADDing a sheet, it is not necessary, as a user would have nothing to do with such sheets prior to their existence.

Beyond that, I am not sure what circumstance(s) you might be thinking of.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

You've asked a similar question in a post I pointed above. Your conclusion there was: "This is excellent, appreciate the feedback and different approaches. Definitely have a solution here."
I wrote one of tips there. If for any reason the user changes sheet's name and you have it's code name known, the code from my answer creates an array of sheets names (dynamic) picked from sheets code names (static). It works for me and seems to be one of possible solutions in your example. Do you have any problem with implementing it?

combo

 

[Randy11]

Hey Combo Thanks for your response. Have tested a number of the solutions and have yet to find one thst will work. If I test with a few sheets seems ok, when I do a large array i get Run Time Error 13, Type Mismatch.
For the sample Code I first provided above recorded in Excel, I replace each "Sheet1", "Sheet2", .... with Sheet1.Name, Sheet2.Name
Run the code and get error noted.

Sheets(Array(Sheet1.Name, Sheet2.Name, Sheet3.Name, Sheet4.Name, Sheet5.Name, Sheet6.Name, Sheet7.Name, _
Sheet8.Name, Sheet9.Name, Sheet10.Name, Sheet11.Name, Sheet12.Name, Sheet13.Name, Sheet14.Name, Sheet15.Name, _
Sheet16.Name, Sheet17.Name, Sheet18.Name, Sheet19.Name, Sheet20.Name, Sheet21.Name, Sheet22.Name, Sheet23.Name, _
, Sheet24.Name, Sheet250.Name)).Select

 

[Randy11]

In one prior suggestion it made reference to a Worksheet Tag Property method where you could group sheets by a Name like Group1, then refer to this group in code. Am running Excel 2007 and can not see this property option, can any provide morwe info on how to use this method?

 

[SkipVought]

So why not create a table of worksheet names and group names to manage whatever process you want to group by sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Skip, per your suggestion, have create a worksheet that has two columns with the sheets names & Group Name. Have named the ranges as Sheet_Names & Group_Names.

Can anyone help me with how I can now utilize this versus an array which I have been trying. Not sure how to call the Group name up in the code.

Would be great if I could see a simple example this would be extremely helpful for the following:

Want to do a simple formatting in cells Range A1:E5 to change to a green fill for sheets 1 - 250 (which I called Group1 in the table)

 

[SkipVought]

What is it that, via VBA, you cannot do one sheet at a time, that you can do as a group?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

would like to format cells A1:E5 with grean fill on Sheet1:Sheet250. If sheet names change or the sheet positions move in the wrkbook want code to continue to work. What is the simplest way to accomplish this.

 

[SkipVought]

This is one way...

dim ws as worksheet

for each ws in worksheets
    select case ws.codename
      case "Sheet1" to "Sheet9", "Sheet10" to "Sheet99", "Sheet100" to "Sheet250"
         'perform formatting
    end select
next

But what does this have to do with GROUPS???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Have only been trying to find a way to perform activities across many worksheets under the conditions noted. One suggestion was to create a group name for the sheets involved. Have also tried using named ranges in the workbook across multiple sheets and call the range in the VBA.
Your help is appreciated as always. Thanks Skip...