Forum Discussion

Dbstedman's avatar
Dbstedman
Brass Contributor
Apr 13, 2022
Solved

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

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!

excel
Excel for web
Macros and VBA
  • amit_bhola's avatar
    amit_bhola
    Apr 18, 2022

    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

     

10 Replies

  • amit_bhola's avatar
    amit_bhola
    Iron Contributor
    Apr 15, 2022

    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
    • Dbstedman's avatar
      Dbstedman
      Brass Contributor
      Apr 18, 2022
      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
      • amit_bhola's avatar
        amit_bhola
        Iron Contributor
        Apr 18, 2022

        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

         

Resources