Selecting & formating worksheets

[Guest]

I have a macro to select worksheets in a workbook but it will only format the
active worksheet and not all:
Sub FormatWS
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
With ActiveSheet.PageSetup
.LeftHeader = ""
....................etc
End With
Next WS
End Sub

Any ideas gratefully received.

 

[Tom Ogilvy]

For Each WS In ThisWorkbook.Worksheets
With WS.PageSetup
.LeftHeader = ""
....................etc
End With
Next WS
End Sub

 

[Guest]

Below is the full macro - as yet I can not get it to work. With WS.PageSetup
has no effect and With activesheet.PageSetup only effects one sheet?

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets ' do something with WS
With WS.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial,Bold""&F"
.CenterFooter = ""
.RightFooter = "&""Arial,Bold""&A"
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
Next WS
End Sub

Kind regards,

Ed.

 

[Tom Ogilvy]

As expected, it worked fine for me on every sheet. I changed

.CenterFooter = ""

to

.CenterFooter = "TESTMESSAGE"

Just to ensure I had a good visual indicator - and worked like a champ. (as
advised and as expected).

You must not be holding your mouth correctly <g>

If you like alot of flash and bang, try:

Sub Tester5()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets ' do something with WS
WS.Activate
With WS.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial,Bold""&F"
.CenterFooter = "TESTMESSAGE"
.RightFooter = "&""Arial,Bold""&A"
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
WS.PrintPreview
Next WS
End Sub