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]

Select the source cell(s) then run the macro.

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
rngD.Resize(rngS.Rows.Count, rngS.Columns.Count).Formula = rngS.Formula
End Sub

 

[irie267]

Sorry, this is not working as I envisioned; it is not allowing me to paste the selection elsewhere. I would like to 1) select/highlight a range, 2) press CTRL SHIFT C to activate the macro to make an exact copy of the formula, and then 3) be able to paste the exact copy of the cell selection somewhere else in the spreadsheet.

 

[footoo]

• Assign the shortcut CTRL+SHIFT+C to the macro
• Select the cell(s) to be copied (Note : if more than one cell is selected, the range must be contiguous)
• Run the macro
• An input box will appear
• Select the destination cell (on the same sheet) and click OK

 

[irie267]

End End If rngD.Resi

• Assign the shortcut CTRL+SHIFT+C to the macro
• Select the cell(s) to be copied (Note : if more than one cell is selected, the range must be contiguous)
• Run the macro
• An input box will appear
• Select the destination cell (on the same sheet) and click OK

This is great, thank you. One more potential tweak (if it's too difficult, please let me know). Is it possible to remove the input box on where to paste and have the macro use excel's native CTRL V shortcut to paste the exact copy?

 

[footoo]

It is possible but don’t have time right now.
Perhaps someone else will work on it.
I think it would involve converting the source selection formula to a string.
Copying it to the clipboard.
Then converting the source back to the formula.
Unfortunately, after CTRL+V, you would then have to manually convert the destination cell from a string to a formula by deleting the leading apostrophe created by the first step.
Someone else might have a better suggestion.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also wondering why you want two ranges on the same worksheet with identical formulas?

 

[footoo]

Try this.

Run this with CTRL+SHIFT+C

Option Explicit
Public rng As Range
Sub Copy_Source_Formula()
Dim cel As Range
Set rng = Selection
Application.ScreenUpdating = False
For Each cel In rng
    cel = "'" & cel.Formula
Next
Selection.Copy
End Sub

Then select the destination cell(s) (do not have to be on the same sheet) and run the following macro with CTRL+SHIFT+V

Sub Paste_Clipboard()
Dim cel As Range
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.Paste
If Err.Number <> 0 Then
    MsgBox "There is nothing to paste."
    End
End If
Selection = Selection.Formula
rng = rng.Formula
Application.CutCopyMode = False
End Sub

 

[irie267]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also wondering why you want two ranges on the same worksheet with identical formulas?

Thank you--I will update accordingly.

Regarding the request, I often times find myself copying a formula entirely from the formula bar and repasting it else and replacing the formula using different inputs. This will save time by removing the copying and pasting action.

 

[irie267]

• Assign the shortcut CTRL+SHIFT+C to the macro
• Select the cell(s) to be copied (Note : if more than one cell is selected, the range must be contiguous)
• Run the macro
• An input box will appear
• Select the destination cell (on the same sheet) and click OK

This solution has actually grown on me--one last question. Is it possible to be able to highlight the cell somehow that the inputbox is selecting as it moves around the sheet? It's hard to see which cell the input box is on.