Camera Function in VBA

The following macro asks you to give a range to be copied, it then copies it as a picture, and prompts user to specify the area to paste it. The lasta part of the macro makes the “photo” dynamic, as if it is done manually with the camera tool. The PasteSpecial pastes the picture, and the picture remains selected. Setting the Formula property for the selection (the picture) results in getting the “photo” dynamic.
-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad.


Option Explicit
Private i, j, k As Integer
Private strMyRange As String
Private sTitle As String
Private myRange As Range
Private NwRange As Range

Sub DoCamera()
Application.ScreenUpdating = True
strMyRange = “Select the range you would like to capture.”
sTitle = “User Input Required”
On Error Resume Next

Set myRange = Application.InputBox(Prompt:=strMyRange, _
Title:=sTitle, Default:=ActiveCell.Address, Type:=8)
If myRange Is Nothing Then End

On Error GoTo 0
myRange.CopyPicture
strMyRange = “Select the range on which you would like to paste.”
sTitle = “User Input Required”
On Error Resume Next
Set NwRange = Application.InputBox(Prompt:=strMyRange, _
Title:=sTitle, Default:=ActiveCell.Address, Type:=8)

If NwRange Is Nothing Then End
On Error GoTo 0
NwRange.PasteSpecial
Selection.Formula = myRange.Address
End Sub

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: