Forum Discussion
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!
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_bholaIron Contributor
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
- DbstedmanBrass ContributorHey 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_bholaIron Contributor
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
- NikolinoDEGold Contributor
Here is a small VBA example, maybe this will help you.
Hope I was able to help you with this file.
I know I don't know anything (Socrates)