Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import selected worksheet from another workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import selected worksheet from another workbook
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! Ladymuck wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to IMPORT a Worksheet From another WorkBook | Excel Worksheet Functions | |||
workbook update each time a new worksheet is selected | New Users to Excel | |||
Update workbook each time a different worksheet is selected | New Users to Excel | |||
How do I import an XLT worksheet into another workbook? | Excel Programming | |||
How can I test when any worksheet within a workbook is selected | Excel Programming |