Printing Each Sheet

[Gary Thomson]

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.

 

[Tom Ogilvy]

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.

 

[Frank Kabel]

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)

 

[Tom Ogilvy]

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.

 

[Elaine Roden]

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