Macro VBA to Copy & Paste Cell Item to another Cell Item

I have a Index/Search Function placed on Col G which returns closest fit data (can be single or many). The Index/Search Function reads item on Cell E2.

1. Using human judgement, i will select the appropriate cell which is best fit (G6)
2. Copy it
3.Position cursor on the Col cell adjacent to one shown in Formula Bar (B46)
4.Paste Special Text

Anyone care to share Macro VBA codeto automate Steps 2 to 4 ?

copy_paste.png

@Mangesh

You have just given me an idea.

1. Can you modify the code below since you have hard coded G6 into it.

Sub Cpypst()
    Range("G6").Copy        ' Copy range G6
    Range("B46").Select      ' Change to required destination to paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False         ' Past special coding
End Sub

Cell to be copied is derived from the position of the cursor on Col. G - modify the above code to do this.

2. Modify the code to show Input Dialog box, where I will specify which Cell i want to Paste Special/Text

In short: the code will do 2 things:

1. Copy the cell identified by cursor position on Col G
2. Show Input Dialog Box where user will manually input value, to Paste Special: Text

Originally Posted by mangesh.mehendale

Hiii..

code is modified to Select both cell i.e. cell to be copy from Col. G with and destination cell to paste special with input box

Sub Cpypst()
    Dim rng As Range
    Dim dest As Range
    Set rng = Application.InputBox("Select Cell in Col G", Type:=8)
    Set dest = Application.InputBox("Destination Cell to Paste", Type:=8)
    rng.Copy        ' Copy range cell in Col G
    dest.Select      ' required destination cell to paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False         ' Paste special coding
    Application.CutCopyMode = False
End Sub

Is this okay...

Yep - Thanks !! Working Fine.