Import selected worksheet from another workbook

[Ladymuck]

I'm stuck on the best way to prompt the user to open another workbook and
select a worksheet to be copied into the file containing the macro. Ideally,
I would hardcode the name of the target worksheet, leaving the user to just
locate the right file.

However, I need to include something that will also allow the user to select
the specific worksheet to copy, just in case there's been some unauthorised
tinkering.

Is this possible?

Many thanks for your help

 

[Dave Peterson]

How about a compromise?

Tell the user to open the "sending" workbook first, then you can ask them to use
the mouse (and the window menu if need be) to select a range that is on that
worksheet.

Option Explicit
Sub testme()
Dim OtherWks As Worksheet

Set OtherWks = Nothing
On Error Resume Next 'in case they hit cancel
Set OtherWks = Application.InputBox _
(Prompt:="Use the window option on the menubar/ribbon " _
& "to change workbooks", _
Title:="Select a cell on the sheet to be used", _
Type:=8).Parent 'the worksheet with the range
On Error GoTo 0

If OtherWks Is Nothing Then
MsgBox "try later"
Exit Sub
End If

MsgBox OtherWks.Name & vbLf & OtherWks.Parent.Name

End Sub

============
Another (classier!) way to do it is to create a userform.

You could use two comboboxes (one for the workbook name and one for the
worksheet names in that workbook)

or
just use a single combobox and include both the workbook and worksheet name in
that dropdown.

If you want to see how that could be done...
Look at Myrna Larson and Bill Manville's compare program:
http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

The code is unprotected, so you could "borrow" as much as you like!