How to make print screen of worksheet

It would be often useful to be able to take a screenshot automatically. Many times I was wondering if it is possible at all to code it. In this article I will share with You how can You make print screen of your worksheet.

Method I

The first idea was to simulate print screen action, using the key on keyboard. I tried to record it, but unfortunately the first line in the saved macro recording was ActiveSheet.Paste and I was not interested in it.

So I had to dug in net a little bit and found SendKeys action, which is, let’s call it, equivalent of print screen key.

Application.SendKeys "({1068})", True

It was really cool solution, but not always worked as it should have. And then I got to this topic, where the guy said about this issue and how he solved it with simple DoEvents.

Application.SendKeys "({1068})", True
DoEvents
ActiveSheet.Paste

Method II

During my researches I also came across with this.

Private Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal _
  bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

One simple line can turn this sub into screenshot.

keybd_event VK_SNAPSHOT, 1, 0, 0

What I also found was that few code lines, which use this sub, can capture the image of currently active application.

keybd_event VK_MENU, 0, 0, 0
keybd_event VK_SNAPSHOT, 0, 0, 0
keybd_event VK_SNAPSHOT, 0, KEYEVENTF_KEYUP, 0
keybd_event VK_MENU, 0, KEYEVENTF_KEYUP, 0

To paste the taken print screen simply go the same way as in method I.

ActiveSheet.Paste

Get the object

After pasting the screen shot the best way to get it is to set it to the variable.

With ws
    Set AltPrintScreen = .Shapes(.Shapes.Count)
End With

Of course remember, that ws must be set worksheet and AltPrintScreen set as shape.

Dim AltPrintScreen as Shape
Dim ws as Worksheet

Above ActiveSheet please change into your worksheet variable.
Also please specify as much variables as it is possible. Thanks to that your code is more stable and reliable.

To sum up

The dreams came true! Now You can actually automate making print screen of worksheet. What was only a wish before, now becomes real.