Here's a macro that allows you to copy the print layout settings from one sheet to another sheet. It assumes that the user is on the source sheet - if not it gives the user the option to abort. It asks the user for each sheet whether they want to copy the properties across from the source sheet. Properties can be commented in and out if needed.
Sub CopyPagePropertiesWithDialog()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim ws As Worksheet
Dim response As VbMsgBoxResult
' Set the source sheet as the active sheet
Set sourceSheet = ActiveSheet
' Inform the user that the source sheet is the current worksheet
response = MsgBox("Is " & sourceSheet.Name & " the source sheet?", vbYesNo)
If response = vbNo Then
MsgBox "Please change to the source sheet.", vbOKOnly
Exit Sub
End If
' Loop through each sheet in the workbook
For Each ws In ActiveWorkbook.Sheets
' Skip the source sheet
If ws.Name <> sourceSheet.Name Then
' Ask the user if they want to copy the page setup to the current sheet
response = MsgBox("Do you want to copy the page setup from " & sourceSheet.Name & " to " & ws.Name & "?", vbYesNoCancel + vbQuestion, "Copy Page Setup")
' Handle the user's response
Select Case response
Case vbYes
' Copy page setup properties
' Comment/uncomment out any properties that you don't want to copy across.
With ws.PageSetup
' .PrintArea = sourceSheet.PageSetup.PrintArea ' Copy Print Range
.PrintArea = "" ' Reset the print area
' .CenterFooter = sourceSheet.PageSetup.CenterFooter
' .CenterHeader = sourceSheet.PageSetup.CenterHeader
' .CenterHorizontally = sourceSheet.PageSetup.CenterHorizontally
' .CenterVertically = sourceSheet.PageSetup.CenterVertically
' .LeftFooter = sourceSheet.PageSetup.LeftFooter
' .LeftHeader = sourceSheet.PageSetup.LeftHeader
' .RightFooter = sourceSheet.PageSetup.RightFooter
' .RightHeader = sourceSheet.PageSetup.RightHeader
' .Orientation = sourceSheet.PageSetup.Orientation
' .PaperSize = sourceSheet.PageSetup.PaperSize
' .PrintGridlines = sourceSheet.PageSetup.PrintGridlines
.PrintHeadings = sourceSheet.PageSetup.PrintHeadings
.PrintTitleColumns = sourceSheet.PageSetup.PrintTitleColumns
.PrintTitleRows = sourceSheet.PageSetup.PrintTitleRows
' .Zoom = sourceSheet.PageSetup.Zoom
.FitToPagesWide = sourceSheet.PageSetup.FitToPagesWide
.FitToPagesTall = sourceSheet.PageSetup.FitToPagesTall
End With
Case vbNo
' Do nothing, move to the next sheet
Case vbCancel
' Exit the loop and the macro
Exit Sub
End Select
End If
Next ws
End Sub