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

macrosmicrosoft excelmicrosoft-excel-2010vba

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

Best Answer

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.

Related Question