Md. Sourov Hossain Mithun
Printing titles on every page in Excel is much easier as there is a built-in command in Excel to do that. But if we want to print titles on every page except the last page then there is no built-in command in Excel. So we must use Visual Basic for Applications (VBA) to perform this kind of specific task. In this article, I’ll show 2 simple VBA Macros to print titles in Excel except the last page following some simple steps.
Print Titles Except for Last Page in Excel: 2 VBA Macros
First of all, get introduced to our dataset which represents some salesperson’s sales in different regions.
Macro 1: Specify Title Row in Codes to Print Titles Except for Last Page
In our first macro, we’ll specify the title rows in the codes so that we don’t need to select them after running the codes. When you work with an Excel file that has the same title row on every sheet then this macro will be helpful for you although you can change it in the codes.
Steps:
- I want to print the dataset in three sheets, so first, we’ll have to insert page breaks. Click on the row from where you want to create a break. I selected row 10.
- Then click as follows: Page Layout > Breaks > Insert Page Break.
Soon after, you will see a line along the upper border of that row.
- Follow the same procedures to insert another page break. I inserted another one before row 15.
Now our dataset is ready to print in three sheets.
- After that, press ALT +F11 to open the VBA window.
- Next, click as follows to insert a new module: Insert > Module.
- Later, type the following codes in the module-
Sub Specify_Title_Row_in_Codes()
Dim AllPages As Long
AllPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
With ActiveSheet.PageSetup
.PrintTitleRows = "$4:$4"
ActiveSheet.PrintOut from:=1, To:=AllPages - 1
.PrintTitleRows = ""
ActiveSheet.PrintOut from:=AllPages, To:=AllPages
End With
End Sub- Then go back to your sheet.
Code Breakdown:
- Here firstly, I started a Sub procedure- Specify_Title_Row_in_Codes.
- Then declare a variable AllPages As Long.
- Then used ExecuteExcel4Macro(“GET.DOCUMENT(50)”) to find the total number of printed pages.
- Next, use PageSetup.PrintTitleRows to determine the row number.
- Finally, PrintOut will print every page with titles except the last page.
- Then just select the macro name and press Run.
Soon after, you will get a dialog box to save the printed PDF file. First, it will save without the last page. Then again the dialog box will appear to save the last page separately.
- Choose your destination folder and type the file name for the pdf file without the last page.
- Finally, just press Save.
- Then set another name to save the last page and press Save.
Here’s the printed first page of the dataset.
It’s the second page, and see, the titles have been added after the page break.
It’s the separate printed pdf of the last page without the titles.
Read More: How to Set Multiple Rows as Print Titles in Excel
Macro 2: Print Titles with Row Selection Except for Last Page
If you have an Excel file that has many titles in different rows then this macro will be useful because after running the codes you will be able to set the title rows by selection before printing.
Steps:
- Follow the 4th and 5th steps from the first method to open the VBA window and insert a module.
- Then write the following codes in it-
Sub Print_Titles_with_Row_Selection()
Dim mPages As Long, I As Integer, mRange As Range
On Error Resume Next
mPages = ActiveSheet.PageSetup.Pages.Count
Set mRange = Application.InputBox("Select Title Rows to Repeat:", "Print Titles Except Last Page", , , , , , Type:=8)
If mRange Is Nothing Then Exit Sub
On Error Resume Next
If mPages > 0 Then
With ActiveSheet.PageSetup
.PrintTitleRows = mRange.AddressLocal
ActiveSheet.PrintOut from:=1, To:=mPages - 1
.PrintTitleRows = ""
ActiveSheet.PrintOut from:=mPages, To:=mPages
End With
End If
End Sub- Next, go back to your sheet.
Code Breakdown:
- First, I created a Sub procedure- Print_Titles_with_Row_Selection.
- After that, declared some variables, mPages As Long, I As Integer, and mRange As Range.
- Then used PageSetup.Pages.Count to count all the pages from the active sheet.
- Later, used InputBox to create an input box to select the title row.
- Finally, PrintOut will print every page with titles except the last page.
- Later, follow the 8th step from the first method to open the Macro dialog box.
- Select the macro name and press Run.
- Soon after, a dialog box will appear to select the title row. Click on the row number and press OK.
- Finally, follow the last three steps from the first method to save the printed pdf file. This macro will also save the last page separately.
Then you will see the output like the image below. It’s our first page.
The second page includes the titles.
Here’s the separate file with the last page without the titles.
Read More: How to Select Column A as Titles to Repeat on Each Page
Download Practice Workbook
You can download the free Excel workbook from here and practice on your own.
Conclusion
That’s all for the article. I hope the procedures described above will be good enough to print titles in Excel except for the last page. Feel free to ask any question in the comment section and please give me feedback.
