copy a worksheet from one across workbooks via dropdown

[krkr8m]

I am attempting to write a macro that will copy one worksheet from the primary named workbook to an existing workbook with a varying name. This can be done manually with an open workbook dropdown list yet any attempt at recording this action only records a copy to a specified (by file name) workbook.

How would I reference all open workbooks so that the user can select one destination workbook from a drop down list?

[Leith Ross]

Hello krkr8m,

Every time a workbook is opened, it is added the the Workbooks Collection Object. As with any Collection Object, you can use the For Each Loop to iterate through each item in the collection. Replace the code in blue with the name of your control.

Example for Forms DropDown Control

Please Login or Register  to view this content.

Example for Control Toolbox ComboBox Control

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[royUK]

Insert a UserForm with a Listbox & Button. In the initialise event create a list of all open workbooks like this

Please Login or Register  to view this content.

Then, add your code to the button to open the workbook,

Please Login or Register  to view this content.

[krkr8m]

Thanks RoyUK! With a little tweaking your help worked out for me. Here is my final code.

Please Login or Register  to view this content.

[royUK]

That's good, but you don't need to Activate or Select the sheets and/or ranges.

[krkr8m]

Yes, I had written it that way the first time with no problems on my own system. It did have problems on other test systems though and so I added them all in which fixed the problem. I probably added more direct selections/activation than necessary yet with such a small macro, I am not worried about it. The difference in proc time required to process with or without the added code is minimal.

Also the final

Please Login or Register  to view this content.

is required for usability. It takes the user to the starting point.

I am also having trouble finding code that will close both the form and the source workbook (sWb). The hide function does not really do what I am looking for. Any help wold be appreciated.