VBA & Macro Problems

How to Auto-Print a Monthly Excel Sheet with Sequential Dates Using VBA

Phi Hung Vo Oct 10, 2026 871 views

Question details

The user needs to automate the process of printing a worksheet for every day of a selected month, ensuring the date cell updates sequentially for each printed copy.

How to Print a Monthly Excel Sheet with Sequential Dates Using a Macro

Product: Excel
Device & OS: not provided
Scenario: Automating the batch printing of daily forms, logs, or reports for an entire month without manually changing the date and clicking print 30 times.
Observed behavior: The user needs an automated VBA solution to iterate through the days of a month, update a specific cell with the date, and print the active sheet automatically.

Before you start

Before you begin, ensure you have enabled the Developer tab in your spreadsheet software to access VBA tools, and verify that you are connected to an active printer with enough paper to handle a full month's worth of prints.

Solution 1Recommended

Use a VBA Macro to Auto-Print Sequential Dates

This automated method loops through every day of the given month, updates your designated date cell, and sends the sheet directly to the printer.

By utilizing Excel's built-in Visual Basic for Applications (VBA), you can create a script that reads a starting date from a specific cell, calculates the number of days in that month, and automatically prints a copy for each day.

Open the VBA Editor

Open your Excel workbook and press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click on Insert, then select Module. This will create a blank canvas for your macro code.

Paste the Print Macro Code

Copy and paste the following code into the module window. This code assumes your date is in cell H2: Sub PrintMonth() Dim d1 As Date Dim d As Date Application.ScreenUpdating = False d1 = Range("H2").Value For d = DateSerial(Year(d1), Month(d1), 1) To DateSerial(Year(d1), Month(d1) + 1, 0) Range("H2").Value = d ActiveSheet.PrintOut Next d Application.ScreenUpdating = True End Sub

Add a Print Button to Your Sheet

Close the VBA editor. Go to the Developer tab on your ribbon, click Insert, and select the Button (Form Control) icon. Draw a button anywhere on your worksheet.

Assign the Macro and Save

When the 'Assign Macro' dialog box appears, select the 'PrintMonth' macro and click OK. Finally, save your workbook as an Excel Macro-Enabled Workbook (.xlsm) so the code functions in the future.

Use a VBA Macro to Auto-Print Sequential Dates

Adjusting the Date Cell: If your date is stored in a different cell, such as A1, simply replace all instances of Range("H2").Value in the VBA code with Range("A1").Value before running the macro.

Advanced Macro Support

Automate Your Printing Workflows with WPS Spreadsheet

WPS Office fully supports VBA macros, allowing you to easily run printing scripts and automate repetitive daily tasks with zero hassle.

1. Open Your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the daily log or report.
2. Access the VBA Editor: Navigate to the Tools tab on the ribbon and click on the Macro button to open the VBA environment.
3. Run the Print Macro: Insert the sequential date printing code into a new module and execute it to instantly batch-print your monthly sheets.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Built-in robust VBA editor for running, managing, and debugging complex macros.Lightweight installation with an intuitive interface that seamlessly matches your existing workflows.

microsoft office alternative - wps office

Frequently Asked Questions

Do I need to enter the exact first day of the month for the macro to work?

No, you can enter any date within your target month into the designated date cell (e.g., H2). The macro utilizes the DateSerial function to automatically determine the first and last day of that specific month.

Why isn't the macro running when I click the assigned button?

Ensure that macros are enabled in your software's Trust Center or Security settings. Additionally, verify that you have saved the workbook in a macro-enabled format (.xlsm) rather than standard .xlsx.

How can I test the code without actually printing 30 physical pages?

To test the macro safely, open the VBA editor and temporarily replace 'ActiveSheet.PrintOut' with 'ActiveSheet.PrintPreview'. This will display the print preview screen for each generated date instead of sending the data directly to your printer.

Excel & Data Problems