Copy a Wks Print Settings to Multiple Wks

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Thanks Zeddy! That was the fix I needed.

Reply | Quote