Macro for Multiple Sheet Print Areas

[lbanham]

Hi,
I need a macro to set the same print area over approx 50 sheets.
Sheet 1, has the print area I want to replicate.
The print area is A1:U190, the sheet needs to be printed on 1 page wide, by two pages tall. It must also page break at row 79.

The sheets are say named Sheet 1 to Sheet 50.

I am using excel 2007.

Any suggestions welcome.

Thanks

 

[shadow12345]

This should do it....

 Sub test()
Dim WorkSh As Worksheet
For Each WorkSh In ActiveWorkbook.Worksheets
    With WorkSh.PageSetup
        .PrintArea = "$A$1:$u$190"
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .FitToPagesWide = 1
        .FitToPagesTall = 2
        .Zoom = False
    End With
Next WorkSh
End Sub

 

[lbanham]

Thanks, that works brilliantly. Is it possible to include a specified page break? I need it to page break at row 79 so it prints in the correct sections.

Thanks again!

 

[lbanham]

Sorry to be a pain, but is it also possible to specify the range of sheets? I have several sheets at the end that shouldnt be formated in this way (i.e i only want to format sheets 1 to 50 and exclude 51-60 etc). I hid them before i ran it, but that didnt work.


Thanks very much

Lynsey