# Multiple Sheets Page Setup

#### Malix

##### New Member
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
This code will copy the worksheet, just give it a name via the input box.
Change the text in red for your sheet name

Code:
``````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``````

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

Does anybody know if there is a solution for this matter?

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.

You can use a macro to simplify this. Record a macro doing the PageSetUp You will get something like this
Code:
``````With ActiveSheet.PageSetup
.PrintTitleRows = "\$1:\$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "\$A\$1:\$F\$20"
With ActiveSheet.PageSetup
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintGridlines = False
.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
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintGridlines = False
.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
Code:
``````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
'etc
End Sub``````

Now each sheet will be setup whenever you print it
lenze

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.

Replies
0
Views
168
Replies
6
Views
85
Replies
3
Views
440
Replies
0
Views
212
Replies
2
Views
192

1,206,756
Messages
6,074,758
Members
446,084
Latest member
WalmitAal

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back