Using user input to select cells to paste into

[neobrainless]

For now I'm trying to just work out how user input works, but my goal is to have a macro which will pause and ask the user to select the cells to paste into and then paste the relevant data where the user has selected.
In a possibly clearer form:

- macro copies data
- macro asks for user to select cell
- macro pastes data into user selected cell


I have this (not working) code so far:

Sub User_Input()
'
' User Input Macro
' Takes input from user to select a cell and paste contents from A1
'

   Dim rng As Range
   Set rng = Application.InputBox(prompt:="Select a cell", Type:=8)
   
   Cells(1, 1).Copy
   Cells(rng).Paste
   
End Sub

Any tips on how I could make this work would be great - could be a basic error as it's been a while since I played with VBA so I'm a little rusty, but I have briefly tried before without much luck!

[ADezii]

The following Code will Copy the contents of Cell A1 into another Single Cell (C7) or Cell Range (B3:M36):

Private Sub CommandButton1_Click()
On Error GoTo Err_CopyCell
Dim strReturn As String

strReturn = Application.InputBox(prompt:="ENTER a Single Cell Address or " & _
                        "Range(A1, F6, B12:M32, etc.)", Type:=2)

If strReturn = "" Then Exit Sub
  
Range("A1").Select
Selection.Copy

Range(strReturn).Select
ActiveSheet.Paste

Exit_CopyCell:
  SendKeys "{ESC}", True    'Remove Copy Indicator
    Exit Sub

Err_CopyCell:
  MsgBox Err.Description, vbExclamation, "Error in Copy Operation"
    Resume Exit_CopyCell
End Sub

[neobrainless]

Erm, that code doesn't give me any errors, but the paste doesn't happen? I get the message box and there is a value shown but when I click 'OK' nothing more happens...

Thanks :)

[NeoPa]

While code is active I'm fairly sure it won't allow operator intervention.

How about getting the operator to select a single cell prior to running the code? Would that work for you?

[neobrainless]

Sadly not, as if they could do that it's not much better than the current manual copy/paste method used... If it's not possible, we'll have to stick with it as is!

[NeoPa]

It should certainly be possible to allow them to specify a range, or even a single cell which is usually enough, but that would involve operator entry of a reference rather than selecting it per se. If that's enough then the process should be relatively straightforward , depending on the Copy & Paste requirements.

[neobrainless]

Hmm. Maybe I'll work on making the text box with the entry request have a selection of possible cell references. For the thing I'm working on now entering cell references should do, but there's a few similar but different cases where user selection by pointing and clicking would be very helpful!

Nevermind, I'll have to find a different workaround, thanks for the help!

[NeoPa]

The problem is that once the code is active the operator cannot interact directly with the workbook. It's possible to interact with a form or even an InputBox type dialog box. Not with the workbook directly though, as far as I'm aware.