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.

Check out the macro category for more useful VBA subroutines.

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.

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:

1.2 Copy picture dialog box - Format Bitmap

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

This is the setting I would choose.

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.

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.

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?

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

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

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

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

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.

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.

Print category

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 […]

Print consecutive page numbers across multiple worksheets
This article describes how to print page numbers in consecutive order through specified worksheets and how to repeat column headers […]

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 […]

Excel categories

Comments (2)