Naming an Object in Excel VBA so it can be selected once it's copied to another sheet/workbook

Question

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

Solution

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.

Comments

[RocketGoal]

Many thanks. I'll go and start working out the picture number and take it from there

[RocketGoal]

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]

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.

[Jonno]

@RocketGoal Glad you resolved it :)

[Scott - Слава Україні]

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.