stuartnoble54
New Member
- Joined
- Feb 3, 2017
- Messages
- 1
Hi Everyone,
I am creating P&L's that pull out numbers on OLE functions from my finance system. The end users of the P&L's do not have the permissions for this and values must be copied and pasted to hard code them.
I am looking for some code that will select multiple ranges of OLE cells, copy and paste as values.
I have been trying to do this by colouring the cells light grey (2) and picking them based on cell colour and found the following code to help identify this. I'm struggling to change this so it will copy and paste rather than display the ranges to me in a pop up - in honesty I don't understand it.
Sub YRange()
Dim c As Range, Yc As Range
For Each c In Range("A15:Z15")
If c.Interior.ColorIndex = 6 Then
If Yc Is Nothing Then
Set Yc = c
Else
Set Yc = Union(Yc, c)
End If
End If
Next c
If Not Yc Is Nothing Then MsgBox Yc.Address(False, False)
End Sub
Any suggestions welcome![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
Thanks in advance
Stuart
I am creating P&L's that pull out numbers on OLE functions from my finance system. The end users of the P&L's do not have the permissions for this and values must be copied and pasted to hard code them.
I am looking for some code that will select multiple ranges of OLE cells, copy and paste as values.
I have been trying to do this by colouring the cells light grey (2) and picking them based on cell colour and found the following code to help identify this. I'm struggling to change this so it will copy and paste rather than display the ranges to me in a pop up - in honesty I don't understand it.
Sub YRange()
Dim c As Range, Yc As Range
For Each c In Range("A15:Z15")
If c.Interior.ColorIndex = 6 Then
If Yc Is Nothing Then
Set Yc = c
Else
Set Yc = Union(Yc, c)
End If
End If
Next c
If Not Yc Is Nothing Then MsgBox Yc.Address(False, False)
End Sub
Any suggestions welcome
Thanks in advance
Stuart