How to find a sheets unique index number

[Steve Whiting]

This has been asked before, but no one seems to have answered it, not that I can find

Question

With ActiveWorkbook.PublishObjects("Steve_15826")

Its the 15826 bit

How can I find out what number a sheet has, so I can refer to different sheets held within the same workbook thus

With ActiveWorkbook.PublishObjects("Steve_nnnnn") where nnnnn is the unique sheet number?

Thanks for any help

 

[KolGuyXcel]

sheets(name of the sheet as a string).index should do.

 

[Steve Whiting]

Thanks, how do you apply it so it's possible to obtain the number?
Some code context please

 

[Steve Whiting]

I need to know how to find the 5-digit number of a sheet, I know my workbook sheet number. It's the _12345 bit I need to know. I hope that's clear

 

[Rick Rothstein]

Try setting your code up like this...

ShtName = "Steve_15826"
Number = Mid(ShtName, Instrrev(ShtName, "_") + 1)
With ActiveWorkbook.PublishObjects(ShtName)

This code replaces the With statement you showed in your original post. You can move the assignment to the Number variable anywhere in the code after the assignment to the ShtName variable. If you Dim your variables (which is always a good idea to do), ShtName should be declared as a String variable and Number can be declared as either a Long unless your numbers could start with a leading zero in which case it would have to be declared as a String variable.

 

[Steve Whiting]

Thanks Rick

I think the point is being missed

Workbook = Steve
Sheet within workbook has been allocated 15286

I need to add sheets to a macro and need to know how to locate what I assume is a unique _number, so that I can perform the same routine on a new data sheet within Same workbook

The workbook contains the same macro, several times the only difference being a number

Eg: Checklists =

Sub PublishChecklists()
'
' Saves htm version of Sheet on Server
'
Range("M8:M2000").Select
Selection.Font.ColorIndex = 0
Selection.autofilter Field:=7, Criteria1:=Array("<>Exc")
Columns("A:J").Select
Selection.EntireColumn.Hidden = True
With ActiveWorkbook.PublishObjects("Steve_15826")
.Title = "CHECKLIST LIST"
.Filename = "\\f:\data\Work\Checklists.htm"
.Publish (True)
.AutoRepublish = False
End With
ChDir "F:\data\Work\Today's Work"
Columns("A:J").Select
Selection.EntireColumn.Hidden = False
Selection.autofilter Field:=7
Columns("H:H").Select
Selection.EntireColumn.Hidden = True
Range("L8").Select

End Sub

Templates =

Sub PublishTemplates()
'
' Saves htm version of Sheet on Server
'
Range("M8:M2000").Select
Selection.Font.ColorIndex = 0
Selection.autofilter Field:=7, Criteria1:=Array("<>Exc")
Columns("A:J").Select
Selection.EntireColumn.Hidden = True
With ActiveWorkbook.PublishObjects("Steve_3496")
.Title = "TEMPLATES LIST"
.Filename = "\\f:\data\Work\Checklists.htm"
.Publish (True)
.AutoRepublish = False
End With
ChDir "F:\data\Work\Today's Work"
Columns("A:J").Select
Selection.EntireColumn.Hidden = False
Selection.autofilter Field:=7
Columns("H:H").Select
Selection.EntireColumn.Hidden = True
Range("L8").Select

End Sub​

--

Both work, the only thing that appears different is the RED NUMER, as they are different sheets
if I add a new sheet, I need to know what the new sheet will be

I inherited the code and have no idea how the Red numbers were found in order for them to be placed in the above code

Many thanks for your time