How do I print all sheets using VBA in Excel? My code doesn't behave like I think it should

Question

I have an Excel workbook with just one sheet to start. Then, there is macro that takes data from that sheet and creates one new sheet for every row in the first sheet. The number of sheets it creates is dynamic and will increase or decrease over time or depending on the filters we set. For example's sake, let's say it results in approximately 20 new sheets for now.

I have another macro that is intended to print all the newly created sheets, but that's where I'm having problems. The macro begins by hiding the first sheet, then it is supposed to select all visible sheets, select a range on all those sheets, and lastly print the selection.

(side note: I used "selection" instead of sheets or workbooks in general because the printed areas wouldn't center on the paper.)

In my head, this is supposed to print out one page for every sheet, but I can't get it to behave properly. When I go through the actions manually to record the macro, everything works -- the print command shows that it wants to print 19 pages. Great! But when I play that same macro back, it only ever wants to print whatever sheet is active and not the rest. Any ideas? Here's my code:

'Declare variables
Dim ws As Worksheet

'Temporarily hide the Customer List so the rest of the workbook can print
Worksheets("Customer List").Visible = False

'Select all visible sheets
For Each ws In Sheets
If ws.Visible Then ws.Select False
Next

'Select the range of cells on whatever sheets is active
Range("A1:J49").Select

'Promp user for which printer to use
Application.Dialogs(xlDialogPrinterSetup).Show

'Page setup
Application.PrintCommunication = False
With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.25)
    .RightMargin = Application.InchesToPoints(0.2)
    .TopMargin = Application.InchesToPoints(0.25)
    .BottomMargin = Application.InchesToPoints(0.25)
    .HeaderMargin = Application.InchesToPoints(0.05)
    .FooterMargin = Application.InchesToPoints(0.05)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = True
    .CenterVertically = True
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintErrors = xlPrintErrorsDisplayed
    .OddAndEvenPagesHeaderFooter = False
    .DifferentFirstPageHeaderFooter = False
    .ScaleWithDocHeaderFooter = True
    .AlignMarginsHeaderFooter = True
    .EvenPage.LeftHeader.Text = ""
    .EvenPage.CenterHeader.Text = ""
    .EvenPage.RightHeader.Text = ""
    .EvenPage.LeftFooter.Text = ""
    .EvenPage.CenterFooter.Text = ""
    .EvenPage.RightFooter.Text = ""
    .FirstPage.LeftHeader.Text = ""
    .FirstPage.CenterHeader.Text = ""
    .FirstPage.RightHeader.Text = ""
    .FirstPage.LeftFooter.Text = ""
    .FirstPage.CenterFooter.Text = ""
    .FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True

'Print all visible sheets
Selection.PrintOut Copies:=1, Collate:=True

'Unhide Customer List after printing and select
Worksheets("Customer List").Visible = True
Worksheets("Customer List").Select
Range("A1").Select

End Sub

AmongTheShadows AmongTheShadows · Accepted Answer · 2021-02-04T07:28:10

I'm still a novice myself but if i was to try this i would set it within a For/Next loop and go based on the sheets count. if you "Customer List" is the first page then you would just bypass it.

AllSheets = Application.Sheets.Count

Application.Dialogs(xlDialogPrinterSetup).Show

For i=2 to AllSheets  '//Assuming sheet 1 is the Customer List
Sheets(i).Range("A1:J49").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.25)
    .RightMargin = Application.InchesToPoints(0.2)
    .TopMargin = Application.InchesToPoints(0.25)
    .BottomMargin = Application.InchesToPoints(0.25)
    .HeaderMargin = Application.InchesToPoints(0.05)
    .FooterMargin = Application.InchesToPoints(0.05)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = True
    .CenterVertically = True
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintErrors = xlPrintErrorsDisplayed
    .OddAndEvenPagesHeaderFooter = False
    .DifferentFirstPageHeaderFooter = False
    .ScaleWithDocHeaderFooter = True
    .AlignMarginsHeaderFooter = True
    .EvenPage.LeftHeader.Text = ""
    .EvenPage.CenterHeader.Text = ""
    .EvenPage.RightHeader.Text = ""
    .EvenPage.LeftFooter.Text = ""
    .EvenPage.CenterFooter.Text = ""
    .EvenPage.RightFooter.Text = ""
    .FirstPage.LeftHeader.Text = ""
    .FirstPage.CenterHeader.Text = ""
    .FirstPage.RightHeader.Text = ""
    .FirstPage.LeftFooter.Text = ""
    .FirstPage.CenterFooter.Text = ""
    .FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
'Print it
Selection.PrintOut Copies:=1
Next

How do I print all sheets using VBA in Excel? My code doesn't behave like I think it should

1 Answers