User selects existing sheet to paste to

[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

 

[Per Jessen]

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

 

[canary2211]

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

 

[Per Jessen]

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

 

[canary2211]

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 -