User selects existing sheet to paste to

  • Thread starter Thread starter canary2211
  • Start date Start date
C

canary2211

Very grateful for any help: I am a novice at this.

I have a spreadsheet with multiple sheets, already named (lets say
Mon, Tues, Wed).

A macro visits other Excel files, retrieves data by copying. I want
to allow the user to select the paste destination (sheet name only,
cells A1 to end). The cells are all in the same format etc, all I need
is to let the user specify - either by typing, or by selecting from a
list, which sheet (Mon,Tue,Wed) to paste to, and then let the macro
continue to paste and do other things.

I have tried :

Range("A1:L6").Select
Selection.copy
Application.InputBox( _
"use mouse to select worksheet", Type:=8)
ActiveSheet.Paste
End Sub

and I have tried

SelectAnswer = InputBox("Tell me a sheet name.")
Worksheets(Answer).Activate
Range("D4").Value = "Done it!"

and I have tried to create a drop down list. All fail! Some error
checking, or a drop down selection, would be helpful to limit user
error but not essential.

PS Working in Excel2007

Thanks in advance
 
Hi
Look at theese two macros. The second code did not work due to a typo error!

Sub aaa()
Dim DestRng As Variant

Set TargetSheet = ActiveSheet
On Error Resume Next
Set DestRng = Application.InputBox( _
"use mouse to select any cell on destination worksheet", Type:=8)
If DestRng Is Nothing Then Exit Sub
On Error GoTo 0
TargetSheet.Range("A1:L6").Copy
Destination:=Sheets(DestRng.Parent.Name).Range("A1")
End Sub


Sub bbb()
SelectAnswer = InputBox("Tell me a sheet name.")
On Error Resume Next
Worksheets(SelectAnswer).Activate
If Err.Number > 0 Then
msg = MsgBox("The sheet: " & SelectAnswer & " does not exists",
vbExclamation + vbOKOnly, "Error")
Exit Sub
End If
On Error Goto 0
Range("D4").Value = "Done it!"

End Sub

Regards,
Per
 
Thank you Per - very helpful.

I must be doing something else wrong as I got syntax error in both of
these :


in aaa at Set DestRng = Application
and in bbb at msg = MsgBox("The sheet: " & SelectAnswer & " does not
exists

I tried to run them on both the destination and target sheets.

Thanks

Nick
 
Hi Nick

I think the error's are due to word wrap in you news reader.

In aaa, the statements below has to be one line in the macro editor:

Set DestRng = Application.InputBox("use mouse to select any cell on
destination worksheet", Type:=8)

and

TargetSheet.Range("A1:L6").Copy
Destination:=Sheets(DestRng.Parent.Name).Range("A1")

In bbb this has to be on one line:

msg = MsgBox("The sheet: " & SelectAnswer & " does not exists",
vbExclamation + vbOKOnly, "Error")

Hopes this helps.
....
Per
"canary2211" <[email protected]> skrev i meddelelsen
Thank you Per - very helpful.

I must be doing something else wrong as I got syntax error in both of
these :


in aaa at Set DestRng = Application
and in bbb at msg = MsgBox("The sheet: " & SelectAnswer & " does not
exists

I tried to run them on both the destination and target sheets.

Thanks

Nick
Click to expand...
 
Brilliant! Works beautifully! Thank you!





Hi Nick

I think the error's are due to word wrap in you news reader.

In aaa, the statements below has to be one line in the macro editor:

Set DestRng = Application.InputBox("use mouse to select any cell on
destination worksheet", Type:=8)

and

TargetSheet.Range("A1:L6").Copy
Destination:=Sheets(DestRng.Parent.Name).Range("A1")

In bbb this has to be on one line:

msg = MsgBox("The sheet: " & SelectAnswer & " does not exists",
vbExclamation + vbOKOnly, "Error")

Hopes this helps.
...
Per














- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...
 
Back
Top