Print Setup Automation (97 SR2)

Topic

New Reply

I want to copy the page setup of sheet1 to all 50 sheets in my workbook. To do so, I am attempting to run the following code:

sub Print_Adjust()
Dim i As Integer
Dim sheetname As Variant
i = 1
Do
sheetname = “sheet” & i
sheetname.Activate
With ActiveSheet.PageSetup
‘Page Setup Code Here
End With
i = i + 1
loop while i < 50
end sub

It's telling me object required, yet the sheetname variable contains the proper syntax, 'Sheet1'… Any suggestions?

Thanks!

Reply | Quote

Replies

Try the following:

Sub Print_Adjust()
Dim I As Integer
For I = 1 To ActiveWorkbook.Sheets.Count
YourCodeHere
Next
End Sub

Reply | Quote

A few comments.

1- If you only need to do this one time, see if This Message will do what you want. Where it says “Go to the sheet you want to apply”, substitute “Select all sheets you want to apply.”

2- When you get it to work, your code is going to be EXTREMELY slow. Changing page setup properties takes forever. You would be better selecting all of the sheets you want to change the properties on and changing them all at one time.

3- To solve the problem you came with, change:

    sheetname.Activate

to

    Worksheets(sheetname).Activate

Reply | Quote

Kel, further to Legare’s second point, here’s a snippet of code I use to apply the PrintArea to multiple Worksheets at once (the error handling may not apply to your code):

Dim oSheet As Worksheet
Dim sPrintRange As String
sPrintRange = Selection.Address
For Each oSheet In ActiveWindow.SelectedSheets
If oSheet.ProtectContents = False And _
TypeName(Selection) = “Range” Then
On Error Resume Next
With oSheet.PageSetup
.PrintArea = sPrintRange
End With
If Err.Number = 1004 Then Exit Sub
End If
Next oSheet

Note that it isn’t necessary to Activate to use .PageSetup. I also recommend that you DIm oSheet as Worksheet, not Variant.
[EDIT: sorry, I misread your code, you aren’t Dimming the Worksheet object as Variant, you are Dimming the Name; never mind! I still think my approach may be better, and if I recall correctly, ’twas Legare who showed me how to do it!]

Reply | Quote

Actually, that is still going to be VERY SLOW since you are doing each sheet individually. I was thinking of something more along the lines of the code below (note that I could not find a way to do this without selecting the sheets):

Public Sub SetWorksheetPageSetup()
Dim strSheetNames() As String
Dim I As Long
Dim oSheet As Worksheet
    I = 0
    For Each oSheet In Worksheets
        ReDim Preserve strSheetNames(0 To I)
        If oSheet.Name  "Sheet1" Then
            strSheetNames(I) = oSheet.Name
            I = I + 1
        End If
    Next oSheet
    Worksheets(strSheetNames).Select
    With ActiveSheet.PageSetup
        .LeftHeader = Worksheets("Sheet1").PageSetup.LeftHeader
        .CenterHeader = Worksheets("Sheet1").PageSetup.CenterHeader
        .RightHeader = Worksheets("Sheet1").PageSetup.RightHeader
        .LeftFooter = Worksheets("Sheet1").PageSetup.LeftFooter
        .CenterFooter = Worksheets("Sheet1").PageSetup.CenterFooter
        .RightFooter = Worksheets("Sheet1").PageSetup.RightFooter
        .LeftMargin = Worksheets("Sheet1").PageSetup.LeftMargin
        .RightMargin = Worksheets("Sheet1").PageSetup.RightMargin
        .TopMargin = Worksheets("Sheet1").PageSetup.TopMargin
        .BottomMargin = Worksheets("Sheet1").PageSetup.BottomMargin
        .HeaderMargin = Worksheets("Sheet1").PageSetup.HeaderMargin
        .FooterMargin = Worksheets("Sheet1").PageSetup.FooterMargin
        .PrintHeadings = Worksheets("Sheet1").PageSetup.PrintHeadings
        .PrintGridlines = Worksheets("Sheet1").PageSetup.PrintGridlines
        .PrintComments = Worksheets("Sheet1").PageSetup.PrintComments
        .PrintQuality = Worksheets("Sheet1").PageSetup.PrintQuality
        .CenterHorizontally = Worksheets("Sheet1").PageSetup.CenterHorizontally
        .CenterVertically = Worksheets("Sheet1").PageSetup.CenterVertically
        .Orientation = Worksheets("Sheet1").PageSetup.Orientation
        .Draft = Worksheets("Sheet1").PageSetup.Draft
        .PaperSize = Worksheets("Sheet1").PageSetup.PaperSize
        .FirstPageNumber = Worksheets("Sheet1").PageSetup.FirstPageNumber
        .Order = Worksheets("Sheet1").PageSetup.Order
        .BlackAndWhite = Worksheets("Sheet1").PageSetup.BlackAndWhite
        .Zoom = Worksheets("Sheet1").PageSetup.Zoom
    End With
End Sub

Reply | Quote

I understand what you mean, but the version I use works pretty fast in practice because each sheet is acted on as an object within an existing collection and never activated or selected. And the code is simple enough for me to understand!

Reply | Quote

It works fast because all you are setting is the print range which does not require going to the printer driver. Try setting something like the margins in that loop and see what happens.

Reply | Quote

Do you really need to run VBA.
I always found that I could set multiple page setups by selecting all sheets together and then setting – the setting applies to them all.

Reply | Quote

Thanks for the assistance all, your feedback worked like a charm!

To answer your question Andrew, the answer among friends would be, “Of course I must VBA, it’s neat, and it makes me look good!” but, the lounge’s answer is, “Doh! I didn’t realize it was that simple.”

Thanks for the input!

Reply | Quote