Print a Specific Number of Pages from Multiple Excel Worksheets with VBA
Question details
Print a specified number of pages from each visible report worksheet using a page count stored in a cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA macro loops through numbered report worksheets and reads the required page count from cell Y6 on each sheet.
- Observed behavior
- The worksheet name must include the loop variable outside the quotation marks so that each numbered sheet can be found.
Check that the worksheets are named in sequence, such as CMM Report (1) and CMM Report (2), and that Y6 contains a positive whole-number page count on each sheet to print. Check each sheet's print area and page setup in Print Preview, because those settings determine how content is divided into pages.
Use a VBA loop to print pages from visible report worksheets
Construct each worksheet name with the loop variable, then print pages 1 through the count in Y6.
The expression Worksheets("CMM Report (" & i & ")") inserts the value of i into the sheet name. The macro stops at the first missing numbered sheet and skips hidden sheets.
In desktop Excel, open the workbook, press Alt+F11, and select Insert > Module.
Paste this code into the module and change OpCount = 5 to the highest report number you want to check: Sub Print_Sheets() Dim i As Long Dim OpCount As Long Dim pageTo As Long Dim ws As Worksheet Dim pageValue As Variant OpCount = 5 For i = 1 To OpCount Set ws = Nothing On Error Resume Next Set ws = Worksheets("CMM Report (" & i & ")") On Error GoTo 0 If ws Is Nothing Then Exit For If ws.Visible = xlSheetVisible Then pageValue = ws.Range("Y6").Value If IsNumeric(pageValue) Then If pageValue >= 1 And pageValue <= 2147483647 Then If pageValue = Fix(pageValue) Then pageTo = CLng(pageValue) ws.PrintOut From:=1, To:=pageTo, Copies:=1 End If End If End If End If Next i End Sub
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), enable macros if prompted and you trust the file, then select Developer > Macros > Print_Sheets > Run.

Manage and print spreadsheets with WPS Office
WPS Office is a free, lightweight alternative for opening, editing, and printing spreadsheets in a familiar interface. Its Excel format compatibility makes it easier to move existing workbooks, although it does not fix Excel's VBA code or guarantee that every macro will run unchanged.

Frequently Asked Questions
Why does VBA look for a sheet named CMM Report (i)?
If i is inside quotation marks, VBA treats it as literal text. Use Worksheets("CMM Report (" & i & ")") to insert the loop variable into the sheet name.
How can I print only visible worksheets?
Check If ws.Visible = xlSheetVisible Then before calling ws.PrintOut. This excludes both hidden and very hidden worksheets.
What happens if a numbered report sheet is missing?
The example stops at the first missing sheet because it uses If ws Is Nothing Then Exit For. Check the worksheet names and numbering before running it.
Where do I change the number of pages to print?
Enter a positive whole number in cell Y6 on each report worksheet. The macro uses that value as the last page in ws.PrintOut, starting at page 1.
How do I check what will appear on each printed page?
Select a report worksheet and open File > Print to preview its pages. Adjust the print area, scaling, or other page settings before running the macro.




