VBA code to copy contents from a cell, and then replace an existing picture with a pic of data

[Dbstedman]

I'm looking for VBA code that could copy cells and paste them as a picture (say Picture 1). I would then like to cut Picture 1 and replace another picture (Picture 2) with Picture 1, while keeping the formatting from Picture 2 (both the location and glow). I'm not sure how naming pictures works in VBA code, but may need to change the names in another line of code to make this a repeatable process.

Initially I used a linked picture, but I would like this dashboard to be visible on Excel for the web and linked pictures are not supported. The data is only refreshed when I run a macro, so adding this to the current macro would be sufficient.   

Note: I cannot make this file a macro enabled worksheet. The macro I built is housed in my personal workbook. I can't make it a macro enabled workbook because it pulls data from a SharePoint list, and .xlsm is not currently supported.

I'm proficient in working with the VBA editor, so exact sheet names and such are not important, I can edit them to fit my data, but I write new code at a 2nd grade level so any help I can get is very appreciated. Thanks!

[#2]

@Dbstedman 

Here is a small VBA example, maybe this will help you.

Hope I was able to help you with this file.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@Dbstedman , refer attached example file and code

For this to work, you need to name the template picture as "PicTemplate" by selecting the template picture and naming it in the name box on the left of formula bar

Sub CopyRangeToPreFormattedPicture()

Dim picPreFormatted As ShapeRange
Set picPreFormatted = ActiveSheet.Shapes.Range("PicTemplate")

Dim rng As Range
Set rng = Range("a1:d5")
rng.Copy
ActiveSheet.Pictures.Paste

Dim picRange As ShapeRange
Set picRange = ActiveSheet.Shapes.Range(ActiveSheet.Shapes.Count)

picPreFormatted.PickUp
With picRange
    .Apply
    .Top = picPreFormatted.Top
    .Left = picPreFormatted.Left
    .Height = picPreFormatted.Height
    .Width = picPreFormatted.Width
End With

picPreFormatted.Delete
picRange.Name = "PicTemplate"

End Sub

[#4]

Hey Amit,
What if my range is on a different sheet? So let's say the range (a1:d5) was on Sheet 1 but my picture (PicTemplate) is on Sheet 2

[#5]

I tried doing Set rng = Sheets("Sheet 1").Range("A1:d5")
That seemed to work. But the code broke on ".apply". Not sure why

[#6]

@Dbstedman 

Work with VBA macros in Excel for the web

Although you can't create, run, or edit VBA (Visual Basic for Applications) macros in Excel for the web, you can open and edit a workbook that contains macros. Any existing macros will remain in the workbook, and you can open the workbook in the Excel desktop app to view and edit the macros.

[#7]

Yeah, I always open the file in the desktop version because it's easier to work with. I don't have problems getting to the VBA editor. I just don't know VBA well enough to always code it correctly.

[#8 · Accepted solution]

@Dbstedman , in that case, replace the ActiveSheet too with the relevant sheet,

Sub CopyRangeToPreFormattedPicture()

Dim picPreFormatted As ShapeRange
Set picPreFormatted = Sheets("Sheet2").Shapes.Range("PicTemplate")

Dim rng As Range
Set rng = Sheets("Sheet1").Range("a1:d5")
rng.Copy
Sheets("Sheet2").Pictures.Paste

Dim picRange As ShapeRange
Set picRange = Sheets("Sheet2").Shapes.Range(Sheets("Sheet2").Shapes.Count)

picPreFormatted.PickUp
With picRange
    .Apply
    .Top = picPreFormatted.Top
    .Left = picPreFormatted.Left
    .Height = picPreFormatted.Height
    .Width = picPreFormatted.Width
End With

picPreFormatted.Delete
picRange.Name = "PicTemplate"

End Sub

[#9]

That you, that part worked, but it didn't copy the formatting (specifically the glow format) nor does it put it in the same location

[#10]

@Dbstedman , so finally did it work? Was the destination picture named PicTemplate while you checked it for another sheet case?

Anyways, attaching the file which was working at my end. Hope it helps.

[#11]

Yes! I made a few more minor adjustments but it is working now. Thanks for the help!