VBA & Macro Problems

How to Insert Page Breaks Based on Cell Values Using a VBA Macro

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs a VBA macro to automatically remove existing page breaks and insert new horizontal page breaks based on specific text markers in a designated column.

Product: Spreadsheets
Device & OS: not provided
Scenario: Automating the print layout formatting of a worksheet by adding page breaks whenever a specific marker (like 'PB') is found, and stopping the script when another marker ('END') is encountered.
Observed behavior: The user wants a script that loops through the used cells in a specific column, evaluates the cell values, resets page breaks, and dynamically inserts new ones at the specified locations.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macro execution in your spreadsheet software's security settings.

Solution 1Recommended

Use a VBA Script to Automate Page Breaks

You can use a custom VBA macro to loop through a specific column, reset all existing page breaks, and insert new horizontal page breaks when a specified text value is detected.

This script uses the last used row in column BM, clears any previous manual page breaks, and scans each cell. When it finds the value 'PB', it inserts a horizontal page break before that row. It exits the process entirely upon finding the value 'END'.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your spreadsheet software.

Insert a New Module

In the Project Explorer window on the left, right-click on your workbook's name, hover over 'Insert', and select 'Module'.

Paste the Macro Code

Copy and paste the following code into the blank module window: Sub InsertPageBreaks() Dim ws As Worksheet Dim cell As Range Dim lastRow As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "BM").End(xlUp).Row ws.ResetAllPageBreaks For Each cell In ws.Range("BM1:BM" & lastRow) If cell.Value = "PB" Then ws.HPageBreaks.Add Before:=cell ElseIf cell.Value = "END" Then Exit For End If Next cell End Sub

Run the Macro

Close the VBA editor or press F5 while inside the module to run the macro. The page breaks will be instantly applied to your active worksheet.

Adjusting the Target Column: If your markers are located in a different column, you must change the 'BM' references in the code to your desired column letter (e.g., 'A' or 'C') before running the macro.

Advanced Spreadsheet Automation

Run VBA Macros Seamlessly in WPS Office

WPS Spreadsheet provides excellent support for VBA macros, allowing you to automate repetitive tasks like custom page breaks and data formatting with ease.

1. Enable Developer Tools: Open WPS Spreadsheet, go to the 'Tools' tab on the ribbon, and ensure your Developer tools are visible.
2. Open the Macro Editor: Click on 'Macro' or press Alt + F11 to launch the integrated VBA editor.
3. Execute Your Script: Paste your custom page break VBA script into a new module and click 'Run' to format your worksheet instantly.

Fully compatible with Microsoft Excel VBA scripts and modulesRobust developer tools available for script editing and debuggingFree and lightweight alternative for powerful spreadsheet automationSeamless management of page layouts and print areas

microsoft office alternative - wps office

Frequently Asked Questions

How do I modify the VBA code to check a different column?

In the provided VBA script, locate the references to column "BM" (e.g., `ws.Range("BM1:BM" & lastRow)`). Change "BM" to the specific letter of the column you want to check, such as "A" or "D".

Why does the macro stop adding page breaks before reaching the end of my data?

The macro is explicitly designed to terminate the scanning loop when it encounters a cell containing the exact text "END". If you need it to process the entire sheet regardless of this marker, you can delete the `ElseIf cell.Value = "END" Then Exit For` lines from the script.

How do I save my workbook after adding this macro?

Standard workbook formats (.xlsx) do not support embedded macros. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown menu to ensure your script is saved properly.

Can I use VBA to insert vertical page breaks instead?

Yes. If you need to break pages vertically across columns, you can modify the script to use `ws.VPageBreaks.Add Before:=cell` instead of `ws.HPageBreaks.Add`.

Excel & Data Problems