VBA & Macro Problems

Print a Specific Number of Pages from Multiple Excel Worksheets with VBA

Natalie TaylorNatalie Taylor Nov 18, 2026 868 views

Question details

Print a specified number of pages from each visible report worksheet using a page count stored in a cell.

Print a Specific Number of Pages from Multiple Excel Worksheets with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In desktop Excel, open the workbook, press Alt+F11, and select Insert > Module.

2
Add the print macro

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

3
Run the macro

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.

Use a VBA loop to print pages from visible report worksheets
Check the print range: The macro skips sheets whose Y6 value is not a positive whole number. It sends pages 1 through the requested page number to the selected printer; the actual output depends on the sheet's print area and pagination.
Free Microsoft Office alternative

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.

Review worksheet content and print settings in WPS SpreadsheetsOpen and edit Microsoft Excel formats, including .xlsx filesUse a familiar spreadsheet interface for everyday workbook tasksMove existing spreadsheet content into a lightweight Office alternative
microsoft office alternative - wps office

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.