How to have user choose cell in another worksheet, and then paste to it, with VBA in Excel

Question

I'm wanting to select a range of cells from one worksheet, push a button, have the user select cells on another sheet in a different workbook, and have the copied cells paste there.

I have the following code:

Private Sub btnCopyPaste_Click()

Dim CopyWS As Worksheet
Set CopyWS = ActiveWorkbook.Sheets("CopySheet")

Dim rngMyRange As Range

    If TypeName(Selection) = "Range" Then
        Set rngMyRange = Selection

            Dim sCell As String
            sCell = Range(Application.InputBox(Prompt:="Pick the Cell", Type:=8)).Value

        rngMyRange.Copy .Range(sCell) 'this is the bad line

    Else
        Exit Sub
    End If

End Sub

I'm trying to user an Application.InputBox to get the "cell to paste in" from the user, but I'm having trouble getting the relevant information about the cell back. Like, the workbook/worksheet location.

When I run the code and select the "cell to paste in" when prompted, the input box on the form shows this: '[WorkbookName]SheetName'!$A$1 ..

Is this information stored in the background, so that I can access it with a command, or will I have to take this string and dissect it?

Thank you for your help!

See the Application.InputBox docs, specifically the paragraph that begins "If Type is 8, InputBox returns a Range object...", and the example immediately following. In fewer words - you should work with a Range object, not a String. — BigBen
Note that the .Value member call will fail if the user cancels that prompt. — Mathieu Guindon
Thank you, I've corrected my code to the Method information: Dim sCell As Range Set sCell = Application.InputBox(prompt:="Pick the cell", Type:=8) However, I'm still getting errors when I'm trying to find the workbook and worksheet name. I've tried more than a few variations ... Range(sCell).Worksheet.Name, sCell.Worksheet.Name, sCell.Address, etc., etc. I get the "invalid procedure call or argument" error. How do you access that information? — lyngineer

Tim Williams Tim Williams · Accepted Answer · 2019-09-27T20:57:19

Something like this should work:

Private Sub btnCopyPaste_Click()

    Dim rngMyRange As Range, c As Range

    If TypeName(Selection) = "Range" Then
        Set rngMyRange = Selection

        On Error Resume Next 'in case user cancels
        Set c = Application.InputBox(Prompt:="Pick the Cell", Type:=8)
        On Error GoTo 0

        If Not c Is Nothing Then rngMyRange.Copy c

    End If

End Sub

How to have user choose cell in another worksheet, and then paste to it, with VBA in Excel

1 Answers