Using a picture using a fixed sheet link

[Throwaway8843]

I am trying to write a macro in VBA whereby I can utilized the linked picture functionality but have the formula of the picture be something like =Sheet1!$G$17:$I$22 instead of the standard =$G$17:$I$22 that way I can copy the linked picture across sheets.

The following formula gives me the required picture, but is not absolute referencing the sheet, just the cells. So if I copy and paste the picture to a new sheet it will look at say cells $G$17:$I$22 in the new sheet instead of sheet1.

Sub LinkedPicture()

Dim cell As Range
Dim rng As Range
Set rng = Selection

For Each cell In rng
If cell.Interior.ColorIndex = xlNone Then
cell.Interior.ColorIndex = xlAutomatic
With cell.Borders
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.149998474074526
.Weight = xlThin
End With
End If

Next cell

Selection.Copy
ActiveSheet.Pictures.Paste(Link:=True).Select

End Sub

 

[Domenic]

Maybe as a workaround you could first paste it into a separate worksheet instead. This will provide you with an absolute reference, which includes a reference to the worksheet. Then you can continue with what you need to do.

 

[Throwaway8843]

Maybe as a workaround you could first paste it into a separate worksheet instead. This will provide you with an absolute reference, which includes a reference to the worksheet. Then you can continue with what you need to do.

An interesting thought. I could try to create a new sheet, paste in, then cut the picture, paste back to the original place, and remove the new sheet. Could work but seems like it might significantly slow down the time taken to run the macro.

 

[Domenic]

While it is somewhat inefficient, it probably won't be as bad as you might expect. Especially if you set ScreenUpdating to False at the beginning of your code, and then set it back to True at the end.

Also, if it's something that you're going to do repeatedly, you could always create a permanent worksheet for this purpose. This way you won't have to keep creating one, and then deleting it. You could hide the worksheet so that that it's not visible.

And, if you set the Visible property to xlSheetVeryHidden, the user won't be able to see it at all, nor will he/she be able to make it visible.