Forum Discussion

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 th...

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

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

Copy Range to a Pre-Formatted Picture.xlsm19 KB

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
  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
  - Dbstedman
    Brass Contributor
    Apr 18, 2022
    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
- Dbstedman
  Brass Contributor
  Apr 18, 2022
  I tried doing Set rng = Sheets("Sheet 1").Range("A1:d5")
  That seemed to work. But the code broke on ".apply". Not sure why
  - NikolinoDE
    Gold Contributor
    Apr 18, 2022
    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.