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: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, usingActiveSheet.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.