Excel: Apply print formatting to all sheets at once

[Static]

I excel if I record a macro of doing this...
or do it manually (Select all sheets.. the pagesetup) it hits all the sheets.

In my VB app it doesnt hit anything but the first sheet!???

Private Sub PrintSetup()
 
For X = 1 To WKBK.Worksheets.Count
    WKBK.Worksheets(X).Select Replace:=False
Next
    WKBK.Worksheets(1).Activate
    With WKBK.ActiveSheet.PageSetup
        .PrintTitleRows = HeadRow '"$3:$12"
        .PrintTitleColumns = HeadCol '"$A:$A"
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "Page &P"
        .LeftMargin = Application.InchesToPoints(xlsLeft)
        .RightMargin = Application.InchesToPoints(xlsRight)
        .TopMargin = Application.InchesToPoints(xlsTop)
        .BottomMargin = Application.InchesToPoints(xlsBottom)
        .HeaderMargin = Application.InchesToPoints(xlsHeader)
        .FooterMargin = Application.InchesToPoints(xlsFooter)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = xlsZoom
    End With
End Sub

[si_the_geek]

Your pagesetup only works with one sheet (WKBK.Worksheets(1).Activate), to make it work with more than one, simply loop thru as you are doing for the select (which by the way, isnt needed), eg:

Private Sub PrintSetup()
 
For X = 1 To WKBK.Worksheets.Count
    With WKBK.Worksheets(X).PageSetup
        .PrintTitleRows = HeadRow '"$3:$12"
...
        .Zoom = xlsZoom
    End With
Next
 
End Sub

It is best not to use ActiveSheet (or similar) as any users working in Excel can change the ActiveSheet

[malik641]

Edit: Didn't notice someone replied...should've hit the refresh button before posting...oops

Well if you want all the worksheets to have the same setup, I don't think you need to select each sheet if you're going to Activate it, so get rid of that line. And also place your Next statement at the end of the Formatting.

And the reason it only selects the first worksheet I can't explain (from your macro recording), but just replace 1 with X and it will setup each sheet the same.

Private Sub PrintSetup()
 
For X = 1 To WKBK.Worksheets.Count
    WKBK.Worksheets(X).Activate
 
    With WKBK.ActiveSheet.PageSetup
        .PrintTitleRows = HeadRow '"$3:$12"
        .PrintTitleColumns = HeadCol '"$A:$A"
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "Page &P"
        .LeftMargin = Application.InchesToPoints(xlsLeft)
        .RightMargin = Application.InchesToPoints(xlsRight)
        .TopMargin = Application.InchesToPoints(xlsTop)
        .BottomMargin = Application.InchesToPoints(xlsBottom)
        .HeaderMargin = Application.InchesToPoints(xlsHeader)
        .FooterMargin = Application.InchesToPoints(xlsFooter)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = xlsZoom
    End With
Next
End Sub

HTH

[Static]

I have had that.. its too slow! the workbooks typically have 30+ sheets...
i excel it works though...??
Macro code

Sheets(Array("Output 36", "Output 35", "Output 34", "Output 33", "Output 32", _
        "Output 31", "Output 30", "Output 29", "Output 28", "Output 27", "Output 26", _
        "Output 25", "Output 24", "Output 23", "Output 22", "Output 21", "Output 20", _
        "Output 19", "Output 18", "Output 17", "Output 16", "Output 15", "Output 14", _
        "Output 13", "Output 12")).Select
    Sheets("Output 36").Activate
    Sheets(Array("Output 11", "Output 10", "Output 9", "Output 8", "Output 7", _
        "Output 6", "Output 5", "Output 4", "Output 3", "Output 2", "Output 1")).Select _
        Replace:=False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 50
    End With

all pages get the formatting!???

[si_the_geek]

In that case, try replicating the code you got from the macro:

Private Sub PrintSetup()
 
Dim SheetNames() as String
ReDim SheetNames(1 To WKBK.Worksheets.Count) as String
    For X = 1 To WKBK.Worksheets.Count
       SheetNames(X) = WKBK.Worksheets(X).Name
    Next
    WKBK.Worksheets(1).Activate
    WKBK.Worksheets(SheetNames).Select Replace:=False
    With WKBK.ActiveSheet.PageSetup
...

[Static]

oo.. didnt try it as an array in the select (I was adding to the selection 1 at a time)... hmmm

be right back

[Static]

wait... now thats strange. If you manually select all sheets... the do page setup it works.. but the recorded macro only hits one sheet.

I may have to loop through...yuk its so slow!

[malik641]

Is this procedure being performed everytime you print?

If so, maybe you should just place this code in a workbook_open event. You'll only have to set it once (unless you manually changed it or another macro changes it after the workbook has been opened).

[Static]

no. I created an external app that does a number of things like renaming sheets, print formatting, hiding/unhinding sheets.. depending on the workbook opened

I think I'll just have to loop through all the sheets bah!