Hey Excellers, it is indeed time for some more #macromonday fun. Today’s Macro will insert a page break in our Excel sheet after a specified number of rows. This is a piece of code I used recently so thought it would be fun to share it with you. I have a report which is issued every month. There are currently three data tables (and sometimes up to 10), on the one worksheet. The report needs to be issued with a data table per page. Each of the data tables had a set amount of rows and columns, in this case, 18. (15 products, a column labels and also I want a blank line between my data tables). A sample of three of my data tables can be seen below.

So, let’s get Excel to the hard work and insert a page break after each of the data tables so they each have their own page.

What Does The Macro Do?

The macro will insert an automatic page break after a specific number of lines or rows on the Excel worksheet. By using the ActiveSheet.HPageBreaks.Add Before we use the HPageBreaks property to return the HPageBreaks collection. By using the Add method we can add a horizontal page break after a specific number of rows.

How Does The Macro Work?

Step 1. Insert a new Module. I want to re-use this macro every month on my report, so I am saving it in my Personal Macro Workbook. This makes it available to any Excel worksheets and books, not just the current one I am working on. If you want to read more about the Personal Macro Workbook then check out my blog posts below.

• Create A Shortcut To Your Personal Macro Workbook
• Macro Mondays – Why Is My Personal Macro Workbook Not Loading Automatically?
• Macro Mondays – How To Delete Your Personal Macro Workbook

Step 2. We need to declare a couple of variables. This simply creates a memory container for these values in Excel’s memory and we specify the type of value for it to expect. In this example, we are specifying our value to be four-byte integers.

Dim LR as Long

Dim i as Long

Step 3. We now tell Excel to find the last active row in the worksheet, so it counts how many rows contain data in Column B.

Step 4. Using the For Next loop we can execute the code a number of times, we specify that it needs to go to row 18 and insert a Line Horizontal line break before this row.

Step 5. Excel loops through the data until it reaches the last row on the worksheet that contains any data.

Step 6. The routine ends.

That’s it, folks. I now have my data tables split into separate pages with a click of a button.

What Next? Want More Excel Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

How To Excel At Excel – Macro Mondays Blog Posts.