Create a Print button – macro

This article describes how to create a button and place it on an Excel worksheet, then assign a macro to that button allowing the user to print the worksheet when the button is pressed on.

1. Create a Print button on a worksheet - macro

A print button located on a worksheet is a great feature that allows the user to quickly print a worksheet. You need a form control button and a macro to build a print button on a worksheet.

What is the biggest downside with a macro assigned to a button?
Most companies have macros disabled because of security reasons.

What is a macro?
A subroutine that contains lines of code made of VBA which is an abbreviation of visual basic of application.

Is it complicated to set up?
No, it is easy and you will learn something new that will be interesting.

1.1 Create a button and place it on the worksheet

The button is found in the Form Controls group, here are the steps:

1. Press with left mouse button on "Developer tab" on the ribbon.
   Developer tab missing?
2. Press with left mouse button on the "Insert" button. A popup menu appears containing multiple controls.
3. Press the "Button (Form Control)" shown on the image above next to the blue arrow.
4. Press and hold with mouse button on the worksheet where you want it located.
5. Drag with mouse while holding the mouse button to adjust the button size.
6. Release the mouse button.
7. A dialog box shows up telling you to assign a macro. Press with left mouse button on "Cancel", we need to assign the macro after putting the code in a module explained below.

Change the button name to Print.

1.2 Create macro

You need the following code to be able to assign the macro to the button.

Sub Macro1()
  Application.Dialogs(xlDialogPrint).Show
End Sub

1.3 Where to put the code?

(The macro shown in the image above is not used here in this article, it only demonstrates where to paste the code.)

1. Press Alt-F11 to open the visual basic editor
2. Press with mouse on "Module" on the "Insert" menu, see the image above.
3. Copy and paste "Sub Macro1" code below to code module.
4. Go back to Excel.

1.4 Assign the macro the button

1. Press with right mouse button on with the mouse on the button. A popup menu appears.
2. Press on "Assign Macro..". A dialog box appears containing available macros to assign.
3. Select the macro you want to assign, in this case Macro1.
4. Press the "OK" button.

Save the Excel file as a *.xlsm in order to keep the macro attached to the workbook. The reason is that a *.xlsx file can't contain macros.

1.5 Watch a video where I explain the steps to insert a print button

2. Create a Print button in a cell

This section describes how to align and resize a print button to a specific cell, the image above shows a print button with the same size as cells H6 and I6.

The instructions here are for those that have already created a print button. If you have not go to section 1 above and create one first.

How to select a form control without running the assigned macro?

1. Press and hold the CTRL button on the keyboard.
2. Select the format control "button".
3. Release the CTRL key.

How to align the button border to a cell border?

1. Make sure the button is already selected, you can tell it is selected if the "handles" around the format control button are shown.
2. Press and hold the "Alt" key.
3. Press and hold with mouse button on one of the "handles".
4. Drag with mouse to the cell border you want to align with. It snaps automatically in place if you get close enough.
5. Repeat with the remaining "handles".

Go back to section 1.2 above and follow the instructions there to complete the setup.

3. Create a Print button on a worksheet without macro

To my knowledge it is not possible to run the "Print" command by pressing a button located on a worksheet without using a macro. However, you have a few other options:

• Create instructions on the worksheet to use the shortcut keys CTRL + P to quickly print a spreadsheet.
• You can easily put a print button on the Quick Access Toolbar.
• Enable a print button on the ribbon.

I will now show you how all three examples above are created.

3.1 Use a text box to inform the user about the shortcut keys CTRL + P

The image above demonstrates a basic text box containing instructions on how to use the shortcut keys to command a print. A text box requires no macro or VBA code. Here is how to create it:

1. Go to tab "Insert" on the ribbon.
2. Press on "Text box" button.
3. Press and hold with mouse button on the worksheet where you want it located. Don't worry, you can move it later on if a mistake is done.
4. Drag with mouse to resize the text box.
5. Release the mouse button.

Press with the mouse on the "text box" to select it.
Press with the mouse on the "text box" text to show the prompt. You can now change the text inside the "text box".
The handles around the "text box" allow you to resize the "text box".
A new tab on the ribbon is shown when the "text box" is selected, it is named "Shape Format". It has settings that allow you to change the background color, the shape outline and so on.

3.2 Print button on the Quick Access toolbar

The image above shows how to create a print button on the Quick Access Toolbar. The downside with this approach is that it is only a customization to your Excel application only, it is not transferred to other users if you send them a copy of your workbook.

1. Press with mouse on the "arrow" button on the top menu bar. A popup menu appears.
   
2. Enable the "Quick Print" button or the "Print Preview and Print". The "Quick Print" button prints the active worksheet without any preview at all.
   

The "Print" button appears on the Quick Access Toolbar.

The "Print Preview and Print" button lets you preview the print before sending it to the printer.

3.3 Enable a print button on the ribbon

The downside with this approach as well is that it is only a customization to your Excel application only. It is not transferred to other users if you send them a copy of your workbook.

1. Press with right mouse button on an empty space on the ribbon. A popup menu appears.
   
2. Press with mouse on "Customize the Ribbon...", a dialog box appears.
   
3. Create a new group in the "Home" tab, see the image above.
4. Rename the group if you like.
   
5. Go to "Choose commands from:" and select the File Tab. See the image above step 1.
6. Scroll down to "Print Preview and Print" and select it.
7. Press the "Add >>" button to move it to the new custom group you just created.
8. Press the OK button to apply changes.

The "Print Preview and Print" button appears on the ribbon.

4. Create a Print button - multiple sheets

The following vba macro is assigned to a print button shown in the image above. It selects the specified worksheets and then shows the print dialog box.

Section 1 above demonstrates how to:

1. Create a button on the worksheet.
2. Where to put the vba macro.
3. How to assign the macro to a print button.
4. Save the workbook as a *.xlsm file to keep the macro attached.

I recommend you follow those steps. Only the macro below is needed.

Sub Print_multiple_ws()

'Dimesnion variables and declare data types
Dim ws_list As Variant

'Specify worksheet names and save array to variable
ws_list = Array("Invoice", "Data")
 
'Select worksheets
Worksheets(ws_list).Select

'Show print dialog box
Application.Dialogs(xlDialogPrint).Show
   
   
End Sub

5. Create a Print button - pdf

This example demonstrates a button on the worksheet that asks for the file name of the PDF to be saved, then saves the PDF and opens it using the default PDF software.

Here is what happens when you press the "Print to PDF" button:

1. A dialog box appears asking for the file name.
2. A PDF is generated, saved, and opened with the default PDF software.

Here is how to build the Print to PDF button:

5.1 Copy vba code

Copy the VBA code below.

 

Sub Print_pdf()

'Dimesnion variables and declare data types
Dim ws_name As Variant

'Ask for the file name
ws_name = InputBox("PDF file name?")

'Check if file name is not empty
If StrPtr(ws_name) <> 0 Then
    
    'Export the worksheet to a PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                    Filename:=ws_name, _
                                    Quality:=xlQualityStandard, _
                                    IncludeDocProperties:=False, _
                                    IgnorePrintAreas:=False, _
                                    OpenAfterPublish:=True
                                
End If

End Sub

 

5.2 Put the vba code in a module

(The vba code shown in the image above is not used here in this example, it only demonstrates where to paste the code.)

1. Press Alt-F11 to open the visual basic editor.
2. Press with left mouse button on Module on the Insert menu.
3. Copy and paste "Sub Print_pdf" code above to code module.
4. Go back to Excel.

5.3 Create the worksheet button

The button is found in the Form Controls group, here are the steps:

1. 1. Press with left mouse button on "Developer tab" on the ribbon.
      Developer tab missing?
   2. Press with left mouse button on the "Insert" button. A popup menu appears containing multiple controls.
   3. Press the "Button (Form Control)" shown on the image above next to the blue arrow.

1. Press and hold with mouse button on the worksheet where you want it located.
2. Drag with mouse while holding the mouse button to adjust the button size.
3. Release the mouse button.
4. A dialog box shows up telling you to assign a macro. Press with left mouse button on "Cancel", we need to assign the macro after putting the code in a module explained below.

Change the button name to "Print to PDF".

6. Create a Print button - selection

This VBA macro is assigned to a print button shown in the image above. It lets you specify a cell range you want to print and then shows the print dialog box.

Section 1 above demonstrates how to:

1. Create a button on the worksheet.
2. Where to put the vba macro.
3. How to assign the macro to a print button.
4. Save the workbook as a *.xlsm file to keep the macro attached.

I recommend you to follow these steps. Only the macro below is needed.

 
Sub Print_selection()

'Dimesnion variables and declare data types
Dim rng As Range

'Define cell range to print
Set rng = Application.InputBox("Select a cell range to print: ", , , , , , , 8)

'Select worksheets
rng.Select

'Show print preview
rng.PrintOut Preview:=True
   
   
End Sub

7. Print screen the entire worksheet

This article demonstrates different techniques on how to take a screenshot of your worksheet. You can do that by simply pressing the PrintScreen key on your keyboard. Paste it into your favorite image editing software and you are done.

What if you want to take a screenshot of a large dashboard or a dataset larger than the screen?
Yes, you can zoom out to a certain extent but eventually the font size will be too small to read. Excel actually allows you to take a screenshot of the selected cell range, whatever size. I will show you how below.

When to take a screenshot of a larger cell range?
It can be very useful as an attachment in an email when you don't want to send the entire workbook.

How to create a macro that automatically saves the screenshot?
I'll also demonstrate a macro, later in this article, that automatically saves the screenshot to a folder you specify. There is a macro-enabled file for you to get at the end of this article.

What's on this section

1. How to take a screenshot of a large area in Excel
   1. Format Picture
   2. Format Bitmap
   3. Include row/column labels
2. Macro recorder
3. Print screen and save the picture to a folder automatically (vba macro)
4. Where to put the code
5. How to run the macro

Check out the macro category for more useful VBA subroutines.

7.1. Screenshot of a large cell area

The image above shows a part of a data set containing random data, here is how to quickly select a larger data set without scrolling.

How to quickly select a large cell range?

1. Press with left mouse button on and select any cell in the data set.
2. Press shortcut keys CTRL + A, this selects the entire data set as long as data cells have no blank rows and/or columns in between.

How to take a screenshot of the selected cell range only?

1. Select the cell range you want to capture a screenshot of.
2. Go to the "Home" tab on the ribbon.
3. Press with left mouse button on the black arrow near the copy button, see image below.
   
4. A popup menu appears. Press with left mouse button on "Copy as picture..".
5. A dialog box appears, here you can select if you want it "As shown on screen" or "As shown when printed".
   

I will explain these settings in greater detail below.

7.1.1 Copy picture dialog box - Format Picture

If you select the radio button named "As shown on screen" you can choose from "Picture" or a "Bitmap".

The "Picture" setting returns a terrible image, shown here:

7.1.2 Copy picture dialog box - Format Bitmap

The "Bitmap" setting returns a much nicer looking image:

This is the setting I would choose.

7.1.3 Copy picture dialog box - As shown when printed

The "As shown when printed" setting returns a really large image and I am not going to show the full-size image here to you, for obvious reasons.

The image above shows the dialog box when the radio button "As shown when printed" is selected. Format - Picture and Bitmap are greyed out.

7.1.3 Copy picture dialog box - include row and column headings

It is possible to include headings but you must select "As shown when printed" and then resize the picture in your image editing software.

How to include row and column headings?

1. Go to tab "Page Setup" on the ribbon.
2. Enable check boxes for Print "Grid lines" and Print "Headings"
   

How to create an image of the selected cell range with column and row headings?

1. Go to tab "Home".
2. Press with left mouse button on the arrow near the copy button, see image below. A popup appears.
   
3. Press with left mouse button on "Copy as Picture...".
   
4. A small dialog box appears. Select radio button "As shown when printed".
   
5. Press with left mouse button on OK button.
6. Paste the picture in your favorite image editing software which allows you to rescale the image to a smaller size.

The image above shows a screenshot with settings "As shown when printed" resized to fit this webpage.

7.2. Copy picture VBA macro

How to record a macro while copying a cell range as a picture?

1. Select the cell range you want to copy.
2. Go to tab "Developer" on the ribbon".
   Developer tab missing?
3. Press the "Record Macro" button. This starts the recorder.

What VBA code does the macro recorder create while taking a screenshot of a cell range?

7.2.1 Bitmap

The images below shows the steps I did while recording a macro.

1. Go to tab "Home" on the ribbon.
2. Press the "Copy" button, a popup menu appears.
   
3. Press on the "Copy as Picture..." with the left mouse button. A dialog box appears.
   
4. Press the radio button next to "Bitmap" to select it.
5. Press the "OK" button.

Go to tab "Developer" and press the Button "Stop Recording".

The following code is generated by the Macro recorder.

Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

7.2.2 Picture

The images below shows the steps I did while recording a macro.

1. Go to tab "Home" on the ribbon.
2. Press the "Copy" button, a popup menu appears.
   
3. Press on the "Copy as Picture..." with the left mouse button. A dialog box appears.
   
4. Press the radio button next to "Picture" to select it if it is not already selected.
5. Press the "OK" button.

Go to tab "Developer" and press the Button "Stop Recording".

The following code is generated by the Macro recorder.

Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

7.2.3 As shown when printed

The images below shows the steps I did while recording a macro.

1. Go to tab "Home" on the ribbon.
2. Press the "Copy" button, a popup menu appears.
   
3. Press on the "Copy as Picture..." with the left mouse button. A dialog box appears.
   
4. Press the radio button next to "As shown when printed" to select it.
5. Press the "OK" button.

Go to tab "Developer" and press the Button "Stop Recording".

The following code is generated by the Macro recorder.

Selection.CopyPicture Appearance:=xlPrinter, Format:=xlPicture

7.3. Print screen and save the picture to a folder automatically

This macro takes a screenshot of the selected cell range and saves the image to c:\temp\temp.gif.

'Name macro
Sub SavePicToFile()

'CopyPicture method with parameters xlscreen and xlBitmap using the selected cell range
Selection.CopyPicture xlScreen, xlBitmap

    'Hides alerts
    Application.DisplayAlerts = False

    'The SET statement allows you to save an object reference to a variable
    'Save a new chart object to variable tmp
    Set tmp = Charts.Add

    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property
    With tmp

        'Delete first series collection
        .SeriesCollection(1).Delete

        'Paste picture
        .Paste

        'Save to filename temp.gif in folder c:\temp with Filtername parameter gif
        .Export Filename:="C:\temp\temp.gif", Filtername:="gif"

        'Delete tmp object
        .Delete
    End With
End Sub

• Set statement
• CopyPicture method
• Seriescollection method

7.3.1 Where to put the code?

1. Press shortcut keys Alt + F11 to open the Visual Basic Editor.
2. Press with left mouse button on "Insert" on the top menu, see image above.
3. Press with left mouse button on "Module" to create a module. The module named Module1 is now visible in the Project Explorer window located to the left in the image above.
4. Copy VBA code.
5. Paste VBA code to empty module.
6. Exit VB Editor and go back to Excel.

7.3.2 How to run the macro?

1. Select the cell range you want to create an image of.
2. Press Alt + F8 to run the "Macro" dialog box, see image above.
3. Press with left mouse button on the name of the macro you want to run, in this case, press with left mouse button on "SavePicToFile".
4. Press with left mouse button on "Run" button.
   The dialog box closes automatically and the macro is then rund.

Check folder c:\temp for an image named temp.gif.

8. Print consecutive page numbers across multiple worksheets

This article explains how to print multiple worksheets so the page numbers are in sequence across all selected worksheets.

The problem is that when you print each worksheet individually, the page numbering resets to 1 for each new worksheet. To overcome this, you can use the methods described below to print specific worksheets or all worksheets with continuous page numbering that spans across all worksheets.

What you will learn in this section:

• How to select multiple worksheets.
• How to enable the "Header and Footer" edit mode.
• How to insert page numbers to the footer of each page.
• How to exit the "Header and Footer" tab.
• How to exit the "Header and Footer" edit mode.
• How to print preview the selected worksheets.
  • Navigate pages in "print preview" mode.
  • Verify page numbers in "print preview" mode.
• How to deselect or "ungroup" worksheets.
  • The issues you may experience if you don't deselect worksheets.

Instructions:

1. Select all worksheets you want to show page numbers on. Press with right mouse button on on one of the tabs located at the bottom left corner of your screen. A pop-up menu appears. Press with mouse on "Select All Sheets."
   
   If you want to only select  a specific range of worksheets, use the SHIFT key. Here is how:
   1. Press with mouse on the first worksheet tab (usually labeled "Sheet1") at the lower left corner of the Excel window.
   2. Press and hold the SHIFT key on your keyboard.
   3. While holding the shift key, press with left mouse button on the last worksheet tab (usually labeled "SheetX", where X is the number of worksheets).
      This will select all worksheets in between the first and last tabs. You can now perform actions on all selected worksheets, such as showing page numbers etc.
      
      The image above shows three highlighted tabs (Ex 2, Ex 3, and array) and one regular tab (Sheet1).Tip!Use the CTRL key on your keyboard to select specific worksheets instead of all worksheets if needed.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Header and Footer" button.
   
4. A new tab named "Header & Footer" appears on the ribbon, you are now in the "Header & Footer" edit mode.
5. Press with left mouse button on "Go to Footer" button. This takes you to one of three boxes in the footer. Press with mouse on one of the boxes where you want the page number to show up.
   
6. Press with left mouse button on "Page number" button. This creates the page number. The "Header & Footer" edit mode shows &[Page] in the selected Footer box.
   
7. Press with left mouse button on in an empty box next to the footer box. Press Escape key on your keyboard to exit the "Header & Footer" tab on the ribbon.
   The "Header & Footer" edit mode is, however, still active. Go to "View" tab on the ribbon", press with left mouse button on the "Normal" button to show the regular worksheet again.
8. Go to "File" on the ribbon.
9. Press with mouse on "Print", a "print preview" box is now visible on the right side of the screen and print settings on the left side of the screen.
10. Check that page numbers are shown at the bottom of the "print preview" box.
    
11. Press with left mouse button on the black arrow at the bottom of the screen to go to the next page, see the image above. Check that all your pages contain page numbers.
12. Press with left mouse button on Print button to print the select worksheets.

The next step is important, make sure you ungroup the select tabs before continuing your work with your workbook. If you don't ungroup tabs (worksheets) after selecting all of them, any changes you make to one worksheet will be applied to all selected worksheets. This can lead to unintended consequences, such as:

1. Formatting changes: If you change the formatting of one worksheet, it will be applied to all selected worksheets.
2. Data changes: If you enter or edit data in one worksheet, it will be replicated in all selected worksheets.
3. Deletions: If you delete data or cells in one worksheet, it will be deleted in all selected worksheets.
4. Insertions: If you insert new rows, columns, or cells in one worksheet, it will be inserted in all selected worksheets.

To avoid these issues, it's essential to ungroup the worksheets. Here is how:

8.2  How to ungroup tabs

1. Press with right mouse button on on one of the tabs. A popup menu appears, see the image above.
2. Press with mouse on "Ungroup Sheets".

The image above shows only one worksheet selected, this means that the tabs are now ungrouped.

Back to top

9. Repeat headers on each page

The image above shows a data table containing four columns: Username, Name, Industry, and State. This section of the article demonstrates how to repeat these header names across all printed papers automatically.

This will improve readability and make it easier to understand the data when readers turn the pages. Instructions:

1. Go to tab "Page Layout" on the ribbon.
2. A dialog box appears.Go to tab "Sheet" on the dialog box.
   
3. Press with left mouse button on the arrow next to field "Rows to repeat at top:".
4. Select the row that contains the header names you want repeat across all printed pages.
5. Press with left mouse button on the "Print preview" button, see the image above.
6. A new window appears named "Print".
7. Use the arrows located at the very bottom to navigate through the pages.
   
8. Verify that each page has the selected column names at the very top.
   
   The image above shows the second page in "print preview", the data has the column names repeated to this page as well.
9. Press the "Print" button to print the pages or press with left mouse button on the back arrow to go back to Excel.

Back to top

10. Remove print preview lines (Page Breaks)

Have you ever wondered how these lines got there on a worksheet?
They are called "print preview lines" and they show where pages will break. In other words, they show how much of the worksheet that will be printed on a single paper or pdf.

Why do print preview lines appear?
They show up after you print or after you go to the print dialog box and then press with left mouse button on the "Cancel" button.

They also appear if you press with left mouse button on the Page Break Preview button on the tab "View" on the ribbon and then press with left mouse button on the "Normal" button, however, they don't disappear automatically.

So how do you get rid of the print preview lines?
You can't easily disable them by press with left mouse button oning a button on the ribbon, you have a few options described below.

What are print preview lines for?
They allow you to preview how your spreadsheet will look when printed. They show page breaks - where one printed page ends and the next will begin. They help identify what data will print on each page.

How to adjust preview lines?

1. Go to tab "View" on the ribbon.
2. Press the "Page Break Preview" button.
   The screen changes and thicker blue lines appear. Move these lines to your needs.
3. Press the "Normal" button to return to the default Excel grid style.

I made a macro, demonstrated later in this article, that you can use to quickly disable the print preview lines, put it in your personal toolbox and use it whenever necessary.

Why use a macro?
You can quickly turn off the "print preview lines" and save a some time.

There is also a macro-enabled workbook for you to get further down in this post. It seems that if you close the workbook and then open it again the print preview lines disappear, however, this is in my opinion to much work to only remove the lines.

10.1. How to remove dotted lines (Excel Options)

1. Press with mouse on tab "File" on the ribbon.
   
2. Press with mouse on "Options"
3. Press with mouse on tab "Advanced".
   
4. Scroll down to "Display Options for this worksheet".
5. Disable check box next to "Show Page Breaks", see the image above.
6. Press with left mouse button on "OK" button to apply changes.

10.2. How to remove dotted lines (Immediate window)

If you are familiar with the Immediate window in the Visual Basic Editor you can probably more quickly delete the print preview lines than through Excel Options.

1. Copy code below.
2. Press Alt+F11 to open the VB Editor.
3. Paste code to the Immediate window
4. Press Enter
5. Return to Excel

ActiveSheet.DisplayPageBreaks = False

10.3. How to remove print preview dotted lines [Excel 2007]

1. Press with left mouse button on the Office button located at the top left side of your screen.
   
2. Press with left mouse button on "Excel Options".
3. Press with left mouse button on tab "Advanced" in the left window.
4. Find "Display Options for this Worksheet".
5. Disable the check box "Show Page Breaks"
6. Press with left mouse button on "OK" button.

10.4. How to remove print preview dotted lines [Excel 2003]

1. Go to the Tools menu.
2. Press with left mouse button on Options.
3. Press with left mouse button on the View tab.
4. Disable checkbox Page Breaks found in Windows Options.
5. Press with left mouse button on OK button.

Excel 2011 Macintosh

1. Go to "Preferences" on the menu.
2. Press with left mouse button on View in "Authoring".
3. Disable checkbox "Show Page Breaks" located below "Windows Options"

10.5. Build a macro and automate these steps

If you often disable print preview lines manually why not build a macro that does it for you? It is not hard, simply copy the macro and paste it to your workbook module.

If you want to use it any workbook you open, put it in a personal macro workbook and link it to the Quick Access Toolbar or the ribbon.

In fact, you can save useful macros in your personal macro workbook and become a lot more efficient in your work.

10.5.1 Macro to disable print preview lines

What happens if we record a macro while disabling "Page Breaks" in Excel options? This is what the macro recorder returns:

Macro1()
ActiveSheet.DisplayPageBreaks = False
End Sub

Use the above line in the examples below if you don't want to toggle print preview lines.

10.5.2 Macro toggles print preview lines on the active worksheet

Using that code you can now show or hide "Page Breaks" on the active sheet with a macro. Meaning, if "Page Breaks" are visible this macro hides them. If "Page Breaks" are hidden, the macro makes them visible.

Sub Macro1()
ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks
End Sub

10.5.3 Macro toggles print preview lines on every sheet in the workbook

The following macro shows or hides "Page Breaks" on every sheet in the current workbook.

Sub Macro2()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
    sh.DisplayPageBreaks = Not sh.DisplayPageBreaks
Next sh
End Sub

10.5.4 Macro toggles print preview lines on every sheet in all open workbooks

This macro toggles "Page Breaks" on every sheet in every open workbook.

Sub Macro3()
Dim wb As Workbook
Dim sh As Worksheet
For Each wb In Workbooks
    For Each sh In wb.Worksheets
        sh.DisplayPageBreaks = Not sh.DisplayPageBreaks
    Next sh
Next wb
End Sub

10.5.5 Watch a video where I explain how to use the macros above

10.5.6 Where to put the code

1. Copy the preferred macro above.
2. Go to Visual Basic Editor (VBE). (Alt + F11)
   
3. Press with left mouse button on "Insert" on the menu.
4. Insert a new module.

Don't forget to save the workbook as a macro-enabled workbook  (*.xlsm file) or the macro is gone the next time you open the workbook.

Here is how to put this macro on the Excel ribbon:

Recommended articles

Get

Form controls category

Macro category

Print category

More than 1300 Excel formulas

Excel categories

Comments (23)