Macro for copy, paste special

Question

Hi,

• I am copying a cell.
• Clicking my target cell
• Then running my macro by a keyboard shortcut.

Sub PstSpc()

    Rpl = InputBox("F: format,  V:Values,  R: Formula")      

 If UCase(Rpl) = "F" Then

        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Else

       If UCase(Rpl) = "V" Then

          Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        Else

        If UCase(Rpl) = "R" Then

          Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

        End If

        End If

   End If

End Sub

I wonder if the macro can be so:

I will run the macro from the beginning when I am on my source cell

the macro will copy the source cell and send a simple message “click the target cell”

then I will be able to click the target cell by mouse

and the macro will ask

   Rpl = InputBox("F: format,  V:Values,  R: Formula")   

and the rest will be the same.

Thanks, regards,

Answer 1

Sub PstSpc() Dim Src As Range Dim Tgt As Range Dim Rpl As String Dim Pst As XlPasteType

    Set Src = Selection

    On Error Resume Next
    Set Tgt = Application.InputBox(Prompt:="Please select the target cell", Type:=8)
    On Error GoTo 0
    If Tgt Is Nothing Then
        Beep
        Exit Sub
    End If

    Rpl = InputBox(Prompt:="F: format,  V:Values,  R: Formula", Default:="V")
    Select Case UCase(Rpl)
        Case "F"
            Pst = xlPasteFormats
        Case "V"
            Pst = xlPasteValues
        Case "R"
            Pst = xlPasteFormulas
        Case Else
            Beep
            Exit Sub
    End Select

    Src.Copy
    Tgt.PasteSpecial Paste:=Pst

    Application.CutCopyMode = False
End Sub

Answer 2

Thank you very much.
Best regards,