macro: with without for - make cell value footer

[oliviar]

Hy Guys,
I have a 'with without for' error.
I want to make the cell 'rightfooter' on sheet1 the value of the right footer on everysheet. But my cycle through macro is producing a with without for error:

Sub rightfooter()
Application.ScreenUpdating = False
    Sheets("sheet2").Visible = True
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        r = Worksheets("Sheet1").Range("rightfooter")
    With ActiveSheet.PageSetup
        .RightFooter = r
    Next ws
    Sheets("sheet1").Select
    Application.ScreenUpdating = True
End Sub

Please help, thanks

 

[Trebor76]

Hi oliviar,

You're missing an End With. Putting this line of code...

End With

...immediately beneath this line of code:

.RightFooter = r

should do the trick.

HTH

Robert

 

[oliviar]

Still issues...

Sub rightfooter1()
Application.ScreenUpdating = False
    Sheets("sheet2").Visible = True
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    With ActiveSheet.PageSetup
    r = Worksheets("Sheet1").Range("rightfooter")
        .RightFooter = r
        End With
    Next ws
    Sheets("sheet1").Select
    Application.ScreenUpdating = True
End Sub

It is only updating the value on sheet 1 and none of the others. ?

 

[Trebor76]

Try this:

Sub Macro3()

    Application.ScreenUpdating = False
    
    Sheets("sheet2").Visible = True
        
    For Each Worksheet In ActiveWorkbook.Worksheets
        With Worksheet.PageSetup
            .RightFooter = Range("rightfooter")
        End With
    Next Worksheet
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert

 

[oliviar]

Thank you that worked.
Plus I found out I had a strange thing happening... my sheet was set to calculate manually. So I also wrote into the macro to force it to be calculating automatically.

 

[Trebor76]

That's good

Unless the code is switching the calculation mode to manual, manually setting it once to automatic should just keep it there. Also, you may not need this line of code...

Sheets("sheet2").Visible = True

...as part of the macro as once the tab is visible it stays visible.