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
  - amit_bhola
    Iron Contributor
    Apr 19, 2022
    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.
    Copy Range to a Pre-Formatted Picture - Different Sheets.xlsm21 KB
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.
  - Dbstedman
    Brass Contributor
    Apr 18, 2022
    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.