VBA / Macro that will Deselect the First Worksheet

[diceicle]

I am looking to create a Macro that selects all the sheets within a file - then it deselects the first sheet regardless of what it is called - and applies a Footer message to the remaining sheets.

I have searched but was unsuccessful in finding a solution I can use.
Thanks for reading.

 

[Zot]

First sheet can be identified as Sheets(1) also, regardless the name

 

[Trebor76]

Hi diceicle,

Welcome to MrExcel!!

As long as the first (furthest left) sheet is not hidden this should do the job:

Option Explicit
Sub Macro1()

    Dim i As Long
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    'Starting from 2 excludes the first (furthest left) sheet in the workbook.
    For i = 2 To ThisWorkbook.Sheets.Count
        Set ws = Sheets(i)
        With ws.PageSetup
            .Orientation = xlPortrait 'Or xlLandscape
            .LeftFooter = "This"
            .CenterFooter = "is the"
            .RightFooter = "Footer"
        End With
    Next i
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[diceicle]

@Trebor76 very much appreciated for that.
I managed to get it working one time but hasn't been firing since. Can't for the life of me figure where I'm going wrong.
I put the code in a Module in my Personel workbook. Ran said Macro and.....nothing.

 

[Trebor76]

Not too sure but at a guess I'd say the code is running on your personal workbook, not the desired workbook.

See if changing this line of code...

For i = 2 To ThisWorkbook.Sheets.Count

to this does the job:

For i = 2 To ActiveWorkbook.Sheets.Count

 

[Zot]

From my experience answering to this forum, ThisWorkbook does not on Excel 365. It still works on my 2016 though and should work on 2010, but as you know anything is probably 99.9% sure with Excel. My workbook runs for year on 2016 32bit failed to run on 2016 64bit. I need to tweak the code ?

 

[diceicle]

Not too sure but at a guess I'd say the code is running on your personal workbook, not the desired workbook.

See if changing this line of code...

For i = 2 To ThisWorkbook.Sheets.Count

to this does the job:

For i = 2 To ActiveWorkbook.Sheets.Count

Very much appreciated. Switching to 'ActiveWorkbook' worked a treat (on my excel 2010). Thanks a million for your help.

 

[Trebor76]

Thanks for letting us know and you're welcome