Printing Grouped Sheets

E

Elaine Roden

Hi there....I'm working on creating a macro in Excel 2000 to be able to print grouped sheets. 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? Do I somehow need to combine the selected sheet info and the selected range and how do I do that

The code to select it works well and the code to print works well, just not together.

Sub PrintAllPayStubs(

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
 
B

Bob Umlas

Us
ActiveWindow.SelectedSheets.PrintOu
instead o
ActiveWindow.SelectedSheets.PrintOu
Selection.Printout
 
E

Elaine Roden

Thanks for trying but I'm afraid that doesn't work for me, because need both the range and the sheets...I used the code you suggested which gives me the whole sheet and we need to print different parts of the sheet at different times...sometimes paystubs...sometimes YTD info...etc. (and up to 50 seasonal employees which come and go so it is important for my user to be able to be able to move the employees to be able to move them in and out of the current group)

Can I include selected sheets and range in the same command?

Elaine
 
T

Tom Ogilvy

for each sh in ActiveWindow.SelectedSheets
sh.Activate
selection.Printout
Next

--
Regards,
Tom Ogilvy


Elaine Roden said:
Thanks for trying but I'm afraid that doesn't work for me, because need
both the range and the sheets...I used the code you suggested which gives me
the whole sheet and we need to print different parts of the sheet at
different times...sometimes paystubs...sometimes YTD info...etc. (and up to
50 seasonal employees which come and go so it is important for my user to be
able to be able to move the employees to be able to move them in and out of
the current group)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top