Exact Copy VBA

[irie267]

Hello All--

I am trying to create a macro that will copy a cell (formula range or one cell) exactly how it is currently written and then be able to paste it elsewhere in the workbook, so that when it's pasted it will result in the same result as the original copied formula. Essentially, I want whatever happens when I press "CTRL C" using the native excel shortcut minus the changing of references (I am aware of locking the reference before copying, but I dont want to have to do that every time). Is this possible in VBA?

 

[footoo]

Put this in the sheet module :

Sub Worksheet_SelectionChange(ByVal Target As Range)
Static lastRng As Range
On Error Resume Next
Target.Interior.ColorIndex = 8
lastRng.Interior.ColorIndex = xlColorIndexNone
Set lastRng = Target
End Sub

Then run this:

Sub InputBoxExample()
Dim rngS As Range, rngD As Range, cel As Range
Set rngS = Selection
On Error Resume Next
Set rngD = Application.InputBox(Prompt:="enter a range", Type:=8)
On Error GoTo 0
If rngD Is Nothing Then
    MsgBox "A range was not selected"
    End
End If
With rngD.Resize(rngS.Rows.Count, rngS.Columns.Count)
    .Select
    .Formula = rngS.Formula
End With
End Sub

 

[irie267]

Put this in the sheet module :

Sub Worksheet_SelectionChange(ByVal Target As Range)
Static lastRng As Range
On Error Resume Next
Target.Interior.ColorIndex = 8
lastRng.Interior.ColorIndex = xlColorIndexNone
Set lastRng = Target
End Sub

Then run this:

Sub InputBoxExample()
Dim rngS As Range, rngD As Range, cel As Range
Set rngS = Selection
On Error Resume Next
Set rngD = Application.InputBox(Prompt:="enter a range", Type:=8)
On Error GoTo 0
If rngD Is Nothing Then
    MsgBox "A range was not selected"
    End
End If
With rngD.Resize(rngS.Rows.Count, rngS.Columns.Count)
    .Select
    .Formula = rngS.Formula
End With
End Sub

This is great, thank you for all the work.

 

[irie267]

I have another issue now. I recently upgraded to Excel 365, and this macro does not work with dynamic ranges; is there a way to tweak the code to not allow dynamic ranges to interfere with the macro? When the macro is used on a dynamic range, it seems to be adding the "@" in the front of the newly pasted formula for some reason.

 

[footoo]

Try :

.Formula2 = rngS.Formula2

 

[irie267]

Try :

.Formula2 = rngS.Formula2

This fixed it--thank you.