Looping Through Worksheet.PageSetup?

[hahdawg]

Hi

I want one worksheet, call it WS2, to have the same PageSetup properties as another worksheet, say WS1, but I can't figure out how to do it.

I tried

WS2.PageSetup=WS1.PageSetup

.

But that didn't work.

I'm assuming I need some kind of loop? I tried a for each loop, but the PageSetup doesn't support the property. I'm really confused. Can anyone help?

 

[drsarao]

AFAIK, object properties need to be copied individually one for one.
This has been working for me:

Sub CopyPageSetup()
Set ps = Sheets("Sheet1").PageSetup 'source sheet
With Sheets("Sheet2").PageSetup 'destination sheet
    .PrintTitleRows = ps.PrintTitleRows
    .PrintTitleColumns = ps.PrintTitleColumns
    .LeftHeader = ps.LeftHeader
    .CenterHeader = ps.CenterHeader
    .RightHeader = ps.RightHeader
    .LeftFooter = ps.LeftFooter
    .CenterFooter = ps.CenterFooter
    .RightFooter = ps.RightFooter
    .LeftMargin = ps.LeftMargin
    .RightMargin = ps.RightMargin
    .TopMargin = ps.TopMargin
    .BottomMargin = ps.BottomMargin
    .HeaderMargin = ps.HeaderMargin
    .FooterMargin = ps.FooterMargin
    .PrintHeadings = ps.PrintHeadings
    .PrintGridlines = ps.PrintGridlines
    .PrintComments = ps.PrintComments
    .PrintQuality = ps.PrintQuality
    .CenterHorizontally = ps.CenterHorizontally
    .CenterVertically = ps.CenterVertically
    .Orientation = ps.Orientation
    .Draft = ps.Draft
    .PaperSize = ps.PaperSize
    .FirstPageNumber = ps.FirstPageNumber
    .Order = ps.Order
    .BlackAndWhite = ps.BlackAndWhite
    .Zoom = ps.Zoom
    .PrintErrors = ps.PrintErrors
    .PrintArea = ps.PrintArea
End With
End Sub

It takes a long time (10 seconds).
I usually delete/comment out the non-relevant/non-critical properties (eg .PrintGridlines, .BlackAndWhite). You can experiment and see which is the fastest and most relevant combination for you.
I don't know whether additional properties have been added after Excel 2003. (You can check by recording a macro while doing Page Setup)

 

[Peter_SSs]

Hi

I want one worksheet, call it WS2, to have the same PageSetup properties as another worksheet, say WS1, but I can't figure out how to do it.

I tried

WS2.PageSetup=WS1.PageSetup

.

But that didn't work.

I'm assuming I need some kind of loop? I tried a for each loop, but the PageSetup doesn't support the property. I'm really confused. Can anyone help?

You can do this manually quite quickly as follows.

1. Select the source sheet first (WS1).

2. Hold Ctrl and select the target sheet(s) (WS2 in your case). This should leave you with 2 sheets selected with WS1 being the active sheet.

3. Open the Page Setup dialog and click OK to close it again.

Done (but don't forget to ungroup your sheets again).


To do this by vba, try this code in a copy of your workbook.
I've added an extra sheet to be copied to so you can see how it works with multiple sheets. Therefore you may want to edit the code to suit your sheet names.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyPageSetup()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsAct <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#007F00">'Record active sheet</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsAct = ActiveSheet<br>    <br>    <SPAN style="color:#007F00">'Stop some of the flicker</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#007F00">'Sheets you want to have the same page setup for</SPAN><br>    Sheets(Array("WS1", "WS2", "abc")).Select<br>    <br>    <SPAN style="color:#007F00">'Sheet to copy page setup FROM</SPAN><br>    Sheets("WS1").Activate<br>    <br>    <SPAN style="color:#007F00">'Open & close the page setup dialog</SPAN><br>    Application.SendKeys "{ENTER}"<br>    Application.Dialogs(xlDialogPageSetup).Show<br>    <br>    <SPAN style="color:#007F00">'Re-activate the originasl active sheet</SPAN><br>    wsAct.Select Replace:=<SPAN style="color:#00007F">True</SPAN><br>    <br>    <SPAN style="color:#007F00">'Screen updating back on</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

It takes a long time (10 seconds).

From Excel 2010 a new property has been added to help with the page setup speed issue

Sub FasterPageSetup()
    Application.PrintCommunication = False
    
    'All the page setup commands here
    
    Application.PrintCommunication = True
End Sub

For earlier versions you can speed page setup by ..

a) record the current printer
b) set the current printer as (say) a pdf printer (some experimentation may be required here)
c) do the page setup macro commands
d) re-set the printer to what it originally was

 

[hahdawg]

Thanks, everyone. Both of those solutions look good.