Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Copy a Wks Print Settings to Multiple Wks

    I would like to copy page and print setting from one worksheet to all other worksheets in my workbook. I can get most of the settings copied by by making the source the active sheet and then selecting all other sheets in the book. Then clicking Page Layout > Page Setup and press Enter seems to copy all the settings . . . except "Rows to repeat at top".

    I've tried this VBA code, but it doesn't assign the Rows 3 & 4 to repeat at the top of subsequent pages. What have I missed?

    Sub LoopThroughSheets2()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets

    With ActiveSheet.PageSetup
    .PrintTitleRows = "$3:$4"
    End With
    On Error Resume Next
    ws.Range("A1") = ws.Name

    Next ws
    End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    You need to use your variable ws instead of ActiveSheet in your loop:

    Sub LoopThroughSheets2()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets

    With ws.PageSetup
    .PrintTitleRows = "$3:$4"
    End With
    On Error Resume Next
    ws.Range("A1") = ws.Name

    Next ws
    End Sub

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    Arcturus16a (2016-01-27)

  4. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Zeddy! That was the fix I needed.

Posting Permissions

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