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

[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.

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