using the Excel generic worksheet names instead of user-given names in code

[Paul]

I would like to be able to write some code to perform operations on
worksheets that doesn't have to worry about what name the user gave to those
worksheets. In the Project Explorer pane of the VB Editor, I notice that
the worksheets are named "Sheet 1, Sheet2, etc. followed by the user-given
names in parenthesis.

I've tried executing commands like "Sheets("Sheet3").Range("myRange").value
= 22 but it doesn't seem to work. More importantly, I'd like to write code
that performs the same operation on multiple worksheets by looping through
the worksheets. If I could get Excel to recognize the generic names, I
could concatenate "Sheet" with the integer range and accomplish my purpose.

Is there a way to get Excel to recognize those sheets as Sheet1, Sheet2,
etc?

thanks in advance,

Paul

 

[Mike H]

Paul,

Apart from the syntax you know there are these ways to refer to a sheet

Refers to the first (Leftmost) sheet
Sheets(1).Range("A1") = "First Sheet"

This use the sheet codename
Sheet1.Range("A2") = "SomeValue"


Mike

 

[Ron de Bruin]

Use

Sheet1.Range("A1").Value = 22

Note : you can change the worksheet code name in the properties window (F4)

 

[Jim Thomlinson]

To directly reference the sheet you can just use.

Sheet1.Range("A1").Value = 22

That being said when you do that you are directly refeencing the sheet
object. What that means is that you can not concatenate a number to the word
sheet to refernce the sheet.

On way

Dim wks as worksheet

for each wks in worksheets
select case wks.name
case sheet1.name, sheet2.name
msgbox "This"
case sheet3.name
msgbox "that"
case else
msgbox "other"
end select
next wks

 

[Paul]

Many thanks to Mike, Ron and Jim!

You've given me all the information I need to handle the worksheets the way
I want.

Jim - Thanks also for the sample code with the Select Case examples.

Paul

 

[Gord Dibben]

Paul

Here's handy little macro to get a list of sheetnames and codenames.

Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
For I = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(I)
.Cells(I, 1).Value = ws.Name
.Cells(I, 2).Value = ws.CodeName
End With
Next I
End Sub


Gord Dibben MS Excel MVP