Userform to specify workbook/worksheet to copy

Question

I would really appreciate any help on the following....

I am looking to create a userform to import external worksheets from open workbooks into the current workbook - My aim is to use 2 drop down lists and one submit button:

First drop down box: Lists all open workbooks - User clicks to specify which is required.

Second box: Lists all worksheets within the selected workbook in the first box - User clicks to specify which is required.

Submit button: When submit is clicked, the macro will take a copy of the workbook/worksheet combination specified in the dropdown boxes and paste this as a new tab in the main workbook.

Thanks in advance.

Answer 1

You can list all workbooks and worksheets in a combo box like they did in this post

You can link the workbook / worksheet combo boxes by 'dependent drop down list', see this post. Note that you need an on-change event fired when the first combo value is selected in order to populate the second combo.

Finally, you can copy worksheets from another workbook as shown in this post