How to take a picture of a specified range in a specified worksheet

[OaklandJim]

Mr. Excel People:

Using VBA I need to take an unlinked "picture" (i.e., like that from the camera tool) of a specific range.

When recording I selected the range then clicked on the camera tool. The recorder said

Activesheet.Shapes.AddShape(, 147#, 133.5, 72#, 72#)

But I do not know how to determine those numbers for the AddShape method.

The worksheet (object) is wsRawDataSheet and the range (object) to take picture of is rAllDataWithHeaders

Thanks a lot!

 

[OaklandJim]

OK so I figured out how to specify Top, Left, Height and Width. I've "created a shape" (rectangle) with default fill color but I cannot figure out how to make the shape a picture of a range like the camera tool.

Maybe I need a different MsoAutoShapeType?

Digression, the way the recorder creates code when I using the camera tool -- without the MsoAutoShapeType parameter -- causes an error.

Activesheet.Shapes.AddShape(, 147#, 133.5, 72#, 72#)

Here is the sub that I have:

Sub MakePicture(prPictureRange As Range)

    Dim dLeft As Double
    Dim dTop As Double
    Dim dHeight As Double
    Dim dWidth As Double
    
    Dim oPicture As Object
    
    With prPictureRange
        dLeft = .Left
        dTop = .Top
        dWidth = .Width
        dHeight = .Height
        .Copy
    End With
    
    Set oPicture = ActiveSheet.Shapes.AddShape(msoShapeRectangle, dLeft, dTop, dWidth, dHeight)
    
    Application.CutCopyMode = False
    
End Sub

 

[OaklandJim]

I have it working now. Here is the key part of the code:

Sub MakePicture(prPictureRange As Range)

    Dim dLeft As Double
    Dim dTop As Double
    Dim dHeight As Double
    Dim dWidth As Double
    
    Dim oPicture As Object
    
    prPictureRange.Copy
    
    Set oPicture = ActiveSheet.Pictures.Paste
    
    Application.CutCopyMode = False
    
End Sub