1

I'm amateurishly putting together macro/VBA in Excel. It involves copying an image, which is of a legend in a different language, from Source.xlsx, and pasting it into Destination.xlsx. The image is just that, it's not an Object anymore in a Chart.

The code below works. However, I'm now trying to align the new image so that it replaces the old xlamLegendGroup in the Destination.xlsx. Currently I'm just pasting it into a cell that is close ("AO6") and manually moving it.

My issue is that I can't select the image once it's in Destination.xlsx. If I could, I'm sure I could work out how to include those X and Y values into my VBA..

I've tried to Name it using the name manager, but my code doesn't recognise it. I've even tried to Name it in VBA during the selection, but I just get errors and incorrect methods, etc. (Like I said, amateur level understanding of VBA)

If any of you could help me solve this Naming and then re-selecting issue, it would be very much appreciated.

Copies an image from soure workbook. It's the only image on the sheet so I can select it this way

Workbooks("MyWkbSource.xlsx").Sheets("LEGEND_AVG").Activate
ActiveSheet.DrawingObjects.Select
Selection.Copy

Activate Destination workbook and pastes image. This is where I would like to align it (by centering it over the top) with the image I'm about to delete.

Workbooks(MyWkbDestination.xlsx").Sheets("AVG").Activate
ActiveSheet.Range("AO6").Select
ActiveSheet.Paste

I select the existing old image and delete it

ActiveSheet.Shapes.Range(Array("xlamLegendGroup")).Select
Selection.Delete

3 Answers 3

0

When you paste an image, it'll likely be given the name Picture #, where # increments by 1 if the item already exists.

As such, the first image you paste should be called Picture 1. You could find out the name of the images currently in your document with:

For Each Shape In ActiveSheet.DrawingObjects
    MsgBox Shape.Name
Next

This would loop through each one and show it in a message box.

As you're just pasting an object, it's not easy to manipulate it in the same sweep. You could use the loop above to find the last Picture #, and then you'd know that the next one pasted would be #+1 and could change the name as required, using ActiveSheet.Shapes.Range(Array("Picture #+1")).Name.

You can also use .Top and .Left to move the image around as needed, as well as .Height and .Width if you need to resize it.

3
  • Many thanks. I'll go and start working out the picture number and take it from there
    – RocketGoal
    Feb 19, 2016 at 16:15
  • Thanks again. In the end luck I managed to select my image using the same name as the one I deleted. more luck than anything else. But it works. See comment to @Gary's student.
    – RocketGoal
    Feb 22, 2016 at 15:28
  • @RocketGoal Glad you resolved it :)
    – Jonno
    Feb 22, 2016 at 15:36
0

Whenever you copy a single Object (say a Shape) from one sheet to another or one workbook to another, you can refer to the destination object by examining its place in .Count. For example:

Sub CopyShape()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim shp1 As Shape, shp2 As Shape
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    Set shp1 = sh1.Shapes(1)

    shp1.Copy
    sh2.Paste

    Set shp2 = sh2.Shapes(sh2.Shapes.Count)
    shp2.Name = "Most recent Shape on Sheet2"
End Sub

Once the copy is complete, the most recent shape placed on the destination will appear last in the list of Shapes on that destination.

1
  • Thank you for this. In the end I discovered that when I pasted in my source DrawingObjects and I deleted the initial xlamLegendGroup that was on the destination sheet, I could select the new 'image' by using xlamLegendGroup. This seems to be luck more than planning.
    – RocketGoal
    Feb 22, 2016 at 15:26
0

Just name the shape with a customized name. Avoid numbers after the name.

Ex: Rectangle 1 change it for> MyRectangle

1
  • 1
    Sounds like a good idea.   How does one do this?   Please do not respond in comments; edit your answer to make it clearer and more complete. Sep 25, 2018 at 0:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .