Printing Each Sheet
 

Hi,

Is there a Macro I can write which will allow me to print
off each sheet within the workbook, such that each sheet
is:

(i) Centred Horizontally;
(ii) Centred Vertically;
(iii) Fits to 1 page wide by 1 page tall;
(iv) Shows Row and Column headings;
(v) Has the Tab name of the worksheet in the centre of the
header, size 16 point Arial.

Many Thanks.

Gary.

Printing Each Sheet
 

Turn on the macro recorder and make the settings you describe.

You will get 99% of the code you need from that.

Then you can run it against each sheet in a loop and print them out.

As part of setting up your custom header, type in a phrase and make it 16 pt

Then modify that in the recording by concatenating in activesheet.Name or
sh.name, whatever is appropriate.

--
Regards,
Tom Ogilvy

"Gary Thomson" wrote in message
...
Hi,

Is there a Macro I can write which will allow me to print
off each sheet within the workbook, such that each sheet
is:

(i) Centred Horizontally;
(ii) Centred Vertically;
(iii) Fits to 1 page wide by 1 page tall;
(iv) Shows Row and Column headings;
(v) Has the Tab name of the worksheet in the centre of the
header, size 16 point Arial.

Many Thanks.

Gary.

Printing Each Sheet
 

Hi Gary
try the following:
- start macro recording (Tools - Macros - Record macro')
- group all your sheets (click on the tab names while holding down the
SHIFT key)
- goto 'File - Pagesetup' and choose your pagesetup
- Print the workbook
- stop recording

After this you may use this macro or simply print the workbook (as you
have applied the format to all sheets)


--
Regards
Frank Kabel
Frankfurt, Germany


Gary Thomson wrote:
Hi,

Is there a Macro I can write which will allow me to print
off each sheet within the workbook, such that each sheet
is:

(i) Centred Horizontally;
(ii) Centred Vertically;
(iii) Fits to 1 page wide by 1 page tall;
(iv) Shows Row and Column headings;
(v) Has the Tab name of the worksheet in the centre of the
header, size 16 point Arial.

Many Thanks.

Gary.

Printing Each Sheet
 

As I recall,
A macro can't use grouped sheets to set the pagesetup.

When you run the macro, only the active sheet in the group is altered.

--
Regards,
Tom Ogilvy


"Frank Kabel" wrote in message
...
Hi Gary
try the following:
- start macro recording (Tools - Macros - Record macro')
- group all your sheets (click on the tab names while holding down the
SHIFT key)
- goto 'File - Pagesetup' and choose your pagesetup
- Print the workbook
- stop recording

After this you may use this macro or simply print the workbook (as you
have applied the format to all sheets)


--
Regards
Frank Kabel
Frankfurt, Germany


Gary Thomson wrote:
Hi,

Is there a Macro I can write which will allow me to print
off each sheet within the workbook, such that each sheet
is:

(i) Centred Horizontally;
(ii) Centred Vertically;
(iii) Fits to 1 page wide by 1 page tall;
(iv) Shows Row and Column headings;
(v) Has the Tab name of the worksheet in the centre of the
header, size 16 point Arial.

Many Thanks.

Gary.

Printing Each Sheet
 

Hi there....I'm working on creating a similar macro, but I would like to be able to print grouped sheet. It works fine when I record it, but when I play it back I always only get one sheet not all the grouped sheets.

This is the code I am using....can you tell me how to make it work

Sub PrintAllPayStubs(

' PrintAllPayStubs Macr
' Macro recorded 5/28/2004 by Elain


Dim sh As Objec
Dim shArr() As Strin
Dim FirstSheet As Objec
Dim LastSheet As Objec

Set FirstSheet = Sheets("aa"
Set LastSheet = Sheets("zz"

ReDim shArr(FirstSheet.Index To LastSheet.Index

For Each sh In ThisWorkbook.Sheet
If sh.Index = FirstSheet.Index And sh.Index <= LastSheet.Index The
shArr(sh.Index) = sh.Nam
End I
Next s

Sheets(shArr).Selec

Range("B1:L59").Selec
With ActiveSheet.PageSetu
.LeftHeader = "
.CenterHeader = "
.RightHeader = "
.LeftFooter = "
.CenterFooter = "
.RightFooter = "
.LeftMargin = Application.InchesToPoints(0.5
.RightMargin = Application.InchesToPoints(0.5
.TopMargin = Application.InchesToPoints(1
.BottomMargin = Application.InchesToPoints(1
.HeaderMargin = Application.InchesToPoints(0.5
.FooterMargin = Application.InchesToPoints(0.5
.PrintHeadings = Fals
.PrintGridlines = Fals
.PrintComments = xlPrintNoComment
.PrintQuality = 35
.CenterHorizontally = Fals
.CenterVertically = Fals
.Orientation = xlPortrai
.Draft = Fals
.PaperSize = xlPaperLette
.FirstPageNumber = xlAutomati
.Order = xlDownThenOve
.BlackAndWhite = Fals
.Zoom = 8
End Wit
Selection.PrintOut Copies:=1, Collate:=Tru
Range("A1").Selec
ActiveWindow.ScrollWorkbookTabs Position:=xlFirs
Sheets("Master").Selec
Range("B6").Selec
End Su

Thank