Setup page layout in VBA

[olympiac]

I am running the code below to setup the Page layout of my spreadsheet.
Do you why it's taking so long when I run it?
Do you know why the line ".CenterFooter = "&[Tab]" instead of giving me the name of the sheet, it gives me the value "[TAB]" in my print preview

With ActiveSheet.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.Orientation = xlLandscape

.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.98)
.BottomMargin = Application.InchesToPoints(0.98)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)

.CenterFooter = "&[Tab]"


Thanks

 

[VoG]

Page setup code tends to run slowly because Excel interacts with the printer drivers. Try this

.CenterFooter = ActiveSheet.Name

 

[olympiac]

Thanks.

 

[jim may]

Add/Modify in RED

.....
.FooterMargin = Application.InchesToPoints(0.5)
T = ActiveSheet.Name
.CenterFooter = T
End With

 

[Peter_SSs]

Those footers will not automatically update if the sheet name is changed. I would suggest this which will auto-update:

.CenterFooter = "&A"

In relation to the speed, VoG has indicated the cause. If you have Excel 2010 you can turn off/on the interaction with the printer to speed the code:

Application.PrintCommunication = False

    With ActiveSheet.PageSetup

        ' Page setup items here

    End With

Application.PrintCommunication = True

In earlier versions you can, with a bit of effort, get around the speed issue by
- recording the current printer
- setting the printer to a printer like a pdf printer (might need a bit of trial & error to see which one works fast)
- doing the page setup
- setting the printer back to the recorded value.

 

[olympiac]

Many thanks.