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
