Copy Sheets to another Workbook based on MultiSelect User Form

So I have a macro that traditionally cycled through a folder of workbooks and copied the first sheet of each workbook into the "master macro workbook."

Now I need that macro to be able to use a user form where the user can select multiple sheets for each workbook and then copy that into the master macro workbook.

I have the user form that populates with the list of sheets and a user can select multiple sheets, however, I don't really know how to pass those selections on to the rest of my macro which then would copy those sheets into the master macro workbook.

Thanks for the help.

Originally Posted by JLGWhiz

It helps to knoiw what kind of control you are using on the user form to list the sheets and what the name of the control is.

Sorry, so a general UserForm and a ListBox called ListBox1 that has the multi-select turned on. Also a command button.

Originally Posted by JLGWhiz

I am a little rusty on the list boxes and user forms but i think something like this in the UF code module might work.

Please Login or Register  to view this content.

Thanks for the reply. That works great, however, I get a subscript out of range if I try to multi-select two or more worksheets? I thought your code iterates through each selection in the list?

Originally Posted by JLGWhiz

It should loop through the list. Check the spelling of the names in the list against the sheet tab names.

I'm not sure if it's spelling since if I choose one sheet (Sheet1) for example, it continues on to the next workbook. I can do the same for another sheet (Sheet2) and it works. It gives me the subscript out of range when I multi-select Sheet1 and Sheet2 which I don't get from looking at you code. It hits me with that error on the copy sheet line.

Try

Please Login or Register  to view this content.

Originally Posted by Fluff13

Try

Please Login or Register  to view this content.

I get the same error and it won't even let me choose one worksheet without throwing the error.

I tried ActiveWorkbook instead, and it gave me the original result - Allows me to choose one worksheet, but errors out when sheets are multi-selected.

My UserForm code has the following code for the Initialize UserForm:

Please Login or Register  to view this content.

Again, sorry I'm not very familiar with how the UserForm functions work.

Originally Posted by Fluff13

Ok, how about

Please Login or Register  to view this content.

Interestingly enough that works!

I'm a little perplexed when ActiveWorkbook added on to the beginning of the error line didn't work but the variable Wbk did work when it equals ActiveWorkbook.

Anyway thanks so much!

You're welcome & thanks for the feedback.

I suspect that when the first sheet copied over, the other workbook became the active workbook & so it was trying to copy from the wrong workbook.