Excel VBA sheet number

[ron513]

I need a VBA statement(s) that will supply a variable with the sheet number of a specific sheet. For example the sheet number of the sheet named Index.

Thanks

Ron

 

[SkipVought]

Hi,

MsgBox Sheets("Index").Index

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ron513]

Thanks Skip.

Ron

 

[SkipVought]

FYI: Keep in mind that the user can move sheets around, thus changing the relationship between sheet name and sheet index.

I much prefer using the Sheet.CodeName that one can ONLY change in the VB Editor.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ron513]

For my application would this be the correct statement?

ShtIndex = Sheets("Index").CodeName

Ron

 

[SkipVought]

How do you expect to use ShtIndex?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ron513]

Skip thanks for sticking with me.

My code will loop though all the sheets in the workbook starting from the sheet named Index to the last sheet, so I’m using the variable ShtIndex for the starting sheet of the loop.

Ron

 

[PHV]

Like this ?
For i = Sheets("Index").Index To Sheets.Count

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ron513]

PH, that works thanks.
I was also trying to get a little education on the Sheet.CodeName that Skip mentioned. It may not apply in this situation but just for future use. I plan on checking into it.

Ron

 

[PHV]

I plan on checking into it
When in the VBE feel free to play with the F2 and F1 keys ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ron513]

I’m a big fan of F1 but did not know about F2. I love this site!
Thanks again.

Ron

 

[SkipVought]

PHV's suggestio will work IF the order of the sheets NEVER CHANGE.

Another approch...

dim ws as worksheet
for each ws in worksheets
  select case ws.name
    case "Index","Include1","Include2"
      ' do something here
    case "Exclude1","Exclude2"
      ' maybe do nothing here
    case else
      ' do things there
  end select
next

You can list the sheets you want to include or list the sheets you want to exclude.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}