Formats many sheets
 

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.

Formats many sheets
 

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

-----Original Message-----
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.

.

Formats many sheets
 

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.


-----Original Message-----
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

-----Original Message-----
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.

.

.

Formats many sheets
 

Hi
simply record a macro while doing this manually :-)

-----Original Message-----
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.


-----Original Message-----
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

-----Original Message-----
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.

.

.

.

Formats many sheets
 

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??)



-----Original Message-----
Hi
simply record a macro while doing this manually :-)

-----Original Message-----
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.


-----Original Message-----
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

-----Original Message-----
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.

.

.

.

.

Formats many sheets
 

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







"Craig McMillan" wrote in message
...
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.