Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print Setup Automation (97 SR2)

    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!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Setup Automation (97 SR2)

    Try the following:

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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Setup Automation (97 SR2)

    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:

    <pre> sheetname.Activate
    </pre>


    to

    <pre> Worksheets(sheetname).Activate
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Print Setup Automation (97 SR2)

    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!]
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Setup Automation (97 SR2)

    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):

    <pre>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
    </pre>

    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Print Setup Automation (97 SR2)

    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! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Setup Automation (97 SR2)

    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.
    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Setup Automation (97 SR2)

    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.

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Setup Automation (97 SR2)

    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!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •