Brilliant! Works beautifully! Thank you!





On Jan 11, 7:59*am, "Per Jessen" wrote:
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" skrev i meddelelsen
news:08d8f3c1-f554-4932-b423-0f0cd1ba6__BEGIN_MASK_n#9g02mG7!__...__END_MASK_i ...
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

On Jan 10, 5:52 pm, "Per Jessen" wrote:



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" skrev i
...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -