How to Reference Worksheet Name vs (Name)

[Randy11]

Have workbook with worksheets that the Tab names are changed from time to time. Am trying to correct VBA code to continue to refer to the same worksheets regardless of the Tab name displayed. Have set up some test code to provide example...

From what I have read, should be able to refer to the Worksheet by it's real name not displayed name by using:
Sheets (Array("(Sheet1)", "(Sheet2)", "(Sheet3)", "(Sheet4)").Select)
This method comes up highlighting the word Sheets & message Compile Error, Invalid use of property
versus below which works but replies on sheet tab name.
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select

Assistance with code appreciated.
Sub Clear_Test()
'
' Clear_Test Macro
'

'
Sheets (Array("(Sheet1)", "(Sheet2)", "(Sheet3)", "(Sheet4)").Select)
Sheets("(Sheet1)").Activate
Range("A1:E9").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C15").Select
Sheets("(Sheet5)").Select
Range("B20").Select
End Sub

 

[SkipVought]

hi,

Worksheet has a

Name property, that is what is displayed on the TAB and the

CodeName property, that is only accessible in VBA or the VB Editor.

Skip,

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

 

[Randy11]

Understood, would like to reference the CodeName property in the code so the tab name has no bearing on the example code. Do not know whow to code this when mutiple sheets are involved.

As stated elsewhere, How could you loop through the codenames, e.g. if your sheet codenames were Year1, Year2, etc, is it possible to write a loop to go through the sheets rather than having to select them individually?

 

[SkipVought]

So what is it that you want to do with various sheets SELECTED, that cannot be done in a loop with each individual sheet?

Skip,

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

 

[Randy11]

There are lots of sheets and many sections of worksheets that are cleared or formated etc.
Is there a way I can group these sheets using CodeName so that all of the selected sheets can have the same areas cleared etc just like the sample I provided. Want to group Sheet1 to Sheet4 together as noted using Codename so I do not have to be concerned about Tab name changes.

Is this possible?

 

[MajP]

You could use the tag property of the worksheet and put in a group number. Then enumerate the worksheets
....
For Each ws In Worksheets
select case ws.tag
case "Group1"
'do something
case "Group2"
'do something
.....
end select
Next ws

 

[SkipVought]

or...

  For Each ws In Worksheets
     select case ws.codename
       case "wsSheet1", "wsSheet3", "wsSheet4"
          'do something
       case "wsSheet2"
          'do something
       .....
     end select
  Next ws

just list out the codenames for what you want to do.

Skip,

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

 

[combo]

You can also get names from code names in the array function:

Sheets(Array(Sheet1.Name, Sheet3.Name, Sheet5.Name)).Select

combo

 

[MajP]

In my solution if you add, delete or modify sheets and want to add them or remove from a group you simply change the tag property. In those solutions you have to go in and modify code for any addition, deletion, modification. I try not to hardwire code where possible.

 

[Randy11]

This is excellent, appreciate the feedback and different approaches. Definitely have a solution here.
Thanks Again for your help!