Format Page setup for multiple worksheet in a workbook

[CB]

I have multiple (over 30) worksheets in a workbook. I need some VBA that
will go and set the following for all worksheets in the workbook:

Font = Arial
Font Style = Regular
Font Size = 10
Left Margin = 1/2"
Right Margin = 1/2"
Top Margin = 1"
Bottom Margin = 1"
Footer = Blank
Header = Sept Sales
Orientation = Landscape
Paper Size = Letter

Can anyone help me automate this as I will have this issue every mont. It
takes a lot of time to format each sheet manually.

Thanks in advance for your help.

Cheers.

 

[Derek P.]

The For Each, and With operations will make this a breeze for you.

Something like this:
Dim current_sheet As Variant

'Loop through each sheet in the workbook
For Each current In ThisWorkbook.Sheets
'Format font
With current.Cells
With .Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With
End With
'Format page setup
With current.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.CenterHeader = "Sept Sales" 'This could and should be modified to
become
'dynamic
.CenterFooter = ""
End With
Next current_sheet


There you go.

D.P.

 

[Derek P.]

Wow there is no means to edit posts?

Sorry i changed one variable name in there from current to current sheet,
but didn't change all of them before posting so that will need to be updated.
Or you can use this version of it instead.

Dim current_sheet As Variant

'Loop through each sheet in the workbook
For Each current_sheet In ThisWorkbook.Sheets
'Format font
With current_sheet.Cells
With .Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With
End With
'Format page setup
With current_sheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.CenterHeader = "Sept Sales" 'This could and should be modified to
become dynamic
.CenterFooter = ""
End With
Next current_sheet

 

[Joel]

Sub Macro1()

For Each sht In Sheets
With sht.Cells.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Bold = False
.Italic = False
.Underline = xlUnderlineStyleNone
End With

With sht.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.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 = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.PrintArea = ""
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Next sht
End Sub

 

[CB]

Thank you so much. I will give it a try.

Cheers.