Multiple Sheets Page Setup

[Malix]

Dear gurus

Please help me how I can set the same page set on multiple worksheets in a workbook.

Currently I am doing it one by one on individual sheets.

Regards
Arshad

 

[redexodia]

This code will copy the worksheet, just give it a name via the input box.
Change the text in red for your sheet name

Sub CopySheet()
    Dim MySheetName As String
    
    
    MySheetName = InputBox("Enter new sheet name")
    
    Sheets("[COLOR=red]Sheet1[/COLOR]").Copy After:=Sheets("[COLOR=red]Sheet1[/COLOR]")
    ActiveSheet.Name = MySheetName
    
    
    
End Sub

 

[Malix]

Dear Gurus

I restate my question. I want to apply the same page setup in one go on multiple worksheets of similar print area.

For example I have 10 worksheets of same size I want 2 pages page setup on all worksheets. Currently I am applying it one by one.

Regards
Arshad Malik

 

[ndimakis]

Does anybody know if there is a solution for this matter?
Thank you in advance!

 

[ac3100]

Bump

 

[Kupo]

Bump

While holding down control, choose all of the sheets, then do the page setup. All the sheets you selected will have the setup you chose.

 

[lenze]

You can use a macro to simplify this. Record a macro doing the PageSetUp You will get something like this

With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$F$20"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 200
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$F$20"
    With ActiveSheet.PageSetup
        .LeftHeader = "Test"
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 200
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
End Sub

. Most of these lines are defaults (unless you changed them) so they can be deleted. Now open the ThisWorkBook module (rightClick the Excel icon to the left of "File" on the menu bar and choose "View Code") and Select "WorkBook" from the left DD and "BeforePrint" from the right DD
Paste the code you recorded in the panel

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$F$20"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
'etc
End Sub

Now each sheet will be setup whenever you print it
lenze

 

[ac3100]

Thanks it worked using the multi tab select and then going straight to page set up.

I was going to page set up via print preview and that method will not work.