VBA - Setting Footers on Opening Workbook

[mickcl]

I am looking to set the Footer for all worksheets on opening of workbook.

Private Sub Workbook_Open()
    For Each ws In ActiveWorkbook.Worksheets
        With ws.PageSetup
            .LeftFooter = " 2 LEFT"
            .CenterFooter = "2 CENTRE"
            .RightFooter = "2 RIGHT"
           End With
   Next
End Sub

Using the code above I get the error 'Objective variable or with block variable not set'. Is this the best way or can it be achieved another way.

Thanks

 

[igold]

Hi,

Does this clear your error...

Private Sub Workbook_Open()
[COLOR=#ff0000]    Dim ws As Worksheet[/COLOR]
    For Each ws In ActiveWorkbook.Worksheets
        With ws.PageSetup
            .LeftFooter = " 2 LEFT"
            .CenterFooter = "2 CENTRE"
            .RightFooter = "2 RIGHT"
           End With
   Next
End Sub

 

[mickcl]

Hi,

I added the extra line and that still doesn't seem to resolve the error.

Regards,

 

[igold]

I don't know what to say, I ran it both ways, got the error with your code, added the declaration and it ran fine. I even double checked in Print Preview and the footers were present on every sheet.

 

[mickcl]

Hi,

I am continuing to work out what is happening.

FYI, In any case when I debug the error it always seem to error on this line: For Each ws In ActiveWorkbook.Worksheets

Regards,

 

[igold]

And the same error message... "Objective variable or with block variable not set"?

I just copied and pasted my code in Post #2 into a new workbook and it ran fine. Perhaps someone with more coding chops will untangle this for us.

Regards,

 

[mickcl]

Hi,

I am concerned that the reason maybe related to the code implementation.

The code is within the personal.xlsm and I wouold like it to run as you open any Workbook to set the Footer.

Sorry, about not mentioning this as I am new to Excel VBA.

Regards,

 

[igold]

I am not savvy enough to know if the implementation is the problem.

Why don't you try writing it into a single workbook and see if that fixes your problem. It's a quick copy and paste to try and problem solve.

Regards,