Nrthugu

Two working macro’s for Checklists & Templates below

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:dataWorkChecklists.htm"

.Publish (True)

.AutoRepublish = False

End With

ChDir "F:dataWorkToday'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:dataWorkTemplates.htm"

.Publish (True)

.AutoRepublish = False

End With

ChDir "F:dataWorkToday'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 "15286" & "3486", as they are different sheets?
if I add a new sheet, I need to know, how I can locate the "3486" number bit as it will be a different number for a new sheet

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

Many thanks for your time

Steve

Just call the same procedure and pass the number to it as an argument.

Your procedure would be similar to:

Sub PublishLists(RefNumber As Long, Title As String)



'...

'...

'...

        With ActiveWorkbook.PublishObjects("Steve_**" & RefNumber & "**")

            .Title = Title & " LIST"



'...

'...

'...



End Sub

You'd then call the procedure and pass the number and title to it:

Sub Test()



    PublishLists 15826, "CHECKLIST"

    PublishLists 3496, "TEMPLATES"



End Sub  

Edit:

I haven't tested any of your code - have just adjusted to accept passed arguments and remove anything that says Select or Selection.

The Test procedure here would execute PublishLists twice using different arguments each time.

Public Sub Test()



    'The three values are passed to the "PublishLists" procedure.

    'First line would PublishObjects to "Steve_**15826**" and second to "Steve_**3496**"



    PublishLists 15826, "CHECKLIST ", "Checklists.htm"

    PublishLists 3496, "TEMPLATES", "Templates.htm"



End Sub



Sub PublishLists(RefNumber As Long, Title As String, SaveFile As String)



    With ThisWorkbook.Worksheets("Sheet1") 'Update sheet name as required.

        With .Range("M8:M2000")

            .Font.ColorIndex = 0

            .AutoFilter Field:=7, Criteria1:=Array("<>Exc")

        End With

        .Columns("A:J").Hidden = True

    End With



    With ThisWorkbook.PublishObjects("Steve_**" & RefNumber & "**") 'RefNumber variable passed to name.

        .Title = Title & " LIST" 'Title variable passed to title.

        .Filename = "\f:dataWork" & SaveFile 'SaveFile variable passed to FileName

        .Publish (True)

        .AutoRepublish = False

    End With



    With ThisWorkbook.Worksheets("Sheet1") 'Update sheet name as required.

        With .Columns("A:J")

            .Hidden = False

            .AutoFilter Field:=7

        End With

        .Columns("H:H").Hidden = True

        .Range("L8").Select 'The only cell that is selected.

    End With



End Sub