Print screen the entire worksheet
This article demonstartes 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's on this page
However, what if you want to take a screenshot of a large dashboard or a dataset? Yes, you can zoom out to a certain extent but eventually the font size will be too small to read.
Believe it or not, Excel actually allows you to take a screenshot of the selected cell range, whatever size. It can be very useful as an attachment in an email when you don't want to send the entire workbook.
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 download at the end of this article.
Check out the macro category for more useful VBA subroutines.
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.
Click and select any cell in the data set. Press shortcut keys CTRL + A, this selects the entire data set as long as data cells have no blank rows in between.
- Select the cell range you want to capture a screenshot of.
- Go to the "Home" tab on the ribbon.
- Click the black arrow near the copy button, see image below.
- A popup menu appears. Click "Copy as picture..".
- 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.
Copy picture - 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:
Copy picture - Format Bitmap
The "Bitmap" setting returns a much nicer looking image:
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 is greyed out.
Include row and column headings
It is possible to include headings but you need to select "As shown when printed" and then resize the picture in your image editing software.
- Go to tab "Page Setup" on the ribbon.
- Enable check boxes for Print "Grid lines" and Print "Headings"
- Go to tab "Home".
- Click the arrow near the copy button, see image below. A popup appears.
- Click "Copy as Picture...".
- A small dialog box appears. Select radio button "As shown when printed".
- Click OK button.
- 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.
Copy picture VBA macro
What VBA code does the macro recorder create while taking a screenshot of a cell range?
Bitmap
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
Picture
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Printer
Selection.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
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
Where to put the code?
- Press shortcut keys Alt + F11 to open the Visual Basic Editor.
- Click "Insert" on the top menu, see image above.
- Click "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.
- Copy VBA code.
- Paste VBA code to empty module.
- Exit VB Editor and go back to Excel.
How to run the macro?
- Select the cell range you want to create an image of.
- Press Alt + F8 to run the "Macro" dialog box, see image above.
- Click the name of the macro you want to run, in this case, click "SavePicToFile".
- Click "Run" button.
The dialog box closes automatically and the macro is then executed.
Check folder c:\temp for an image named temp.gif.
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]
Repeat headers on every page you print
If you want each page to have the same row(s) or column(s) to be repeated at every page you print. […]
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. Instructions: Go […]
2 Responses to “Print screen the entire worksheet”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Great tip!
Thanks for sharing!
Hi oscar
let me know onething why did you delete seriescollection from chart
it looks good but curios to know why did u delete seriescollectio while taking screenshot