EXCEL1: Setup Print specs

[aacod]

I need a code to SETUP the page on 31 sheets with a click.
Each tab labelled as 1 Mar, 2 Mar and so on till 31 Mar.

With page settings on the page setup window with following specs.

Orientation: Landscape view
Scaling Adjust: to 55%

and on the Sheet Tab:

Print Area A1:N54

Print preview: Margins adjusted to print in center of the page

 

[VoG]

This will apply the settings to every worksheet. If you want to exclude certain sheets please state their names.

Sub PrtSet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    With ws.PageSetup
        .PrintArea = "A1:N54"
        .CenterHorizontally = True
        .Orientation = xlLandscape
        .Zoom = 55
    End With
Next ws
End Sub

 

[Smitty]

See if this does what you want:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> SetupAllSheets()<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>        <SPAN style="color:#00007F">With</SPAN> ws.PageSetup<br>            .Orientation = xlLandscape<br>            .Zoom = 55<br>            .AlignMarginsHeaderFooter = <SPAN style="color:#00007F">True</SPAN><br>            .FirstPage.CenterHeader.Text = "Center Header"<br>            .FirstPage.CenterFooter.Text = "Center Footer"<br>            .PrintArea = "$A$1:$N$54"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ws<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[aacod]

The first code works great as I want it .

The second one gives an error as : Object does'nt this property or method.


Thanks to both of you.

I love this site.

 

[aacod]

The first code works great as I want it .

The second one gives an error at first as : 'Object does'nt support this property or method' and then is ok as I go and view the print preview page.


Thanks to both of you.

I love this site.

 

[Smitty]

I have Excel 2007, so if you're using an earlier version it probably doesn't like the CenterFooter portion.

 

[aacod]

Thanks Smitty, using excel 2003

 

[Smitty]

That'd be the problem then. I really need to go back and uninstall 2007, so I can install 2003 and 2007 side-by-side. If I ever get time (which probably won't happen...)

 

[Smitty]

That'd be the problem then. I really need to go back and uninstall 2007, so I can install 2003 and 2007 side-by-side. If I ever get time (which probably won't happen...)