Moving from sheet to sheet

[ed123]

Hiya,

I am having trouble with the following coding. I want to format sheets in a workbook one after the other. The coding below will do it to only the active sheet and will not move from one to the next:


Sub FormatExcelSheet()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets ' do something with WS
With ActiveSheet.PageSetup
.PrintTitleRows = ""
etc...
End With
Next WS
End Sub

Can anyone help - it would be much appreciated??

Ed.

[johnske]

Hi Ed,

Welcome to VBAX. Does this do what you want?


Sub FormatExcelSheet()
Dim WS As Worksheet
ActiveSheet.Cells.Select
Selection.Copy
For Each WS In ThisWorkbook.Worksheets
WS.Activate
Range("A1").Select
ActiveSheet.Paste
Next WS
End Sub

(you have to activate each sheet)
HTH

John :)


PS If you mean to do something other than copy and paste, just change the code where appropriate...

[ed123]

Hi John,

Thanks but not quite (failing in my question & not your answer!). I want to adjust the page setup on each sheet and not just the format of each sheet.


Sub FormatExcelSheet()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
With ActiveSheet.PageSetup
.PrintTitleRows = ""
etc...
End With
Next WS
End Sub

This adjusts the pagesetup of one sheet but not the next.

Kind regards,

Ed:help

[johnske]

Hi Ed,

You probably read my reply before I edited it... The main problem is you are saying


For Each WS In ThisWorkbook.Worksheets
With ActiveSheet.PageSetup

The "With ActiveSheet" you have always refers to your first sheet because you're not activating each new sheet after the "For Each WS...", you need to put "WS.Activate" as in the example I gave. :hi:

PS To put your code as mine is, put (vba) before the code and (/vba) (but use square brackets) at the end of it - it makes it a lot easier to read

Regards,
John

[mdmackillop]

Try



For Each sh In Sheets
With sh.PageSetup
.Orientation = xlLandscape
End With
Next

[Norie]

Ed

Try this


Sub FormatExcelSheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets ' do something with WS
With ws.PageSetup
.PrintTitleRows = ""
'etc...
End With
Next WS
End Sub

[ed123]

Thanks to all that helped with this query. The following code appears to work and moves from sheet to sheet to change the page setup:



For Each sh In Sheets
With sh.PageSetup
.Orientation = xlLandscape
End With
Next



Kind regards,

Paul