Formats many sheets

[Craig McMillan]

Hi,

Is there a way I can do the following for all sheets in a
workbook (i.e. using a Macro), without having to manually
do it for each one??

Center the sheet horizontally and vertically (for
Printout);
Include Row and Column Headings (for Printout);
Fit each sheet to 1 page wide by 1 page tall (for Printout)
Makes each sheet Landscape (for Printout);
Makes the left and right margins 0.9 inches each (for
Printout);
Adjust zoom size for each sheet to 85%;
Remove gridlines from each sheet;
Include a header in each sheet, on the left, that has the
tab name of the sheet in Arial, 14 point, bold.

Many Thanks,

Craig.

 

[Frank Kabel]

Hi
a non VBA way:
- first group all the required sheets (hold down the SHIFT
key and select the sheets with your mouse)
- apply your formating

 

[Craig McMillan]

Frank,

This is OK, but for just about every workbook that I use,
I apply the below-mentioned properties.

Can someone supply me with the VBA way???

Thanks,

Craig.

 

[Frank Kabel]

Hi
simply record a macro while doing this manually

 

[Craig]

Ok!!!

What do I put at the start of the Macro??

Dim sh as Worksheet
For each sh in Worksheets
..
..
..
Next

???

(I'm a bit of a novice at this - can you tell??)

 

[Paulw2k]

Hi Craig

One way would be to cycle through each sheet and apply the settings, but XL
can be vary slow doing this.
Slightly quicker is selecting all the sheets and apply these generic
settings.

Copy the procedure below into your "Personal.xls". I have not applied any
error checking code but it should do the business in most cases.


Now I think that deserves a beer! I'll have a Hoegaarden, please. (A nice
Belgian beer)

Regards

Paul


Sub FormatALLSelectedWorksheetsForPrinting()
Dim sht As Worksheet

Rem Collect all the worksheets together.
For Each sht In ActiveWorkbook.Worksheets
sht.Select False
Next


Rem The workbook in now in "Group" mode.
Rem The settings below will apply to all those selected sheets.
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&14&A"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.PrintHeadings = True
.PrintGridlines = False
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
With ActiveWindow
.Zoom = 85
.DisplayGridlines = False
End With
Rem Get out of "Group" mode.
Sheets(1).Select



End Sub