VBA to Split Workbook: No Longer Working with Multi-Selection on User Form

Having solved the values issue in my previous thread, I now have a new problem.

Previous thread: https://www.excelforum.com/excel-pro...to-values.html

The code I have used to work as I wished: I was able to CTRL select one or more sheets in the user form at the beginning and these would be copied and split out as separate files. Now, if I multi-select, it only works for the first sheet, after which I get copies of the last tab in the workbook instead of the other sheets I've selected. The subsequent sheets are copied, but when it comes to splitting out, it doesn't happen.


Only this line of code executes correctly on the subsequent sheets:

Please Login or Register  to view this content.

What do I need to tweak to get it to loop back to the splitting out section for each sheet? I am not able to upload a workbook at the moment as I am at work.

My code is here:

Please Login or Register  to view this content.

Posted answer in previous thread.

My bad.

Put this part

Please Login or Register  to view this content.

just above next.

I'll try it - thanks!

It's a good job I had a backup of the file!

It has worked, BUT it has deleted many worksheets from the source file!

OK.

BTW Thanks for rep+ on previous thread, much, much appreciated.

Damn. Should slow myself down and look better. Sorry.

Please Login or Register  to view this content.

No, this doesn't quite work - sorry.

For the first sheet I have selected, it copies out the LAST sheet in the workbook. For the second and third sheets I selected at the same time, it works. It has also left a copy of the last sheet selected in the source workbook.

Nearly but not quite!

Made a dummy file to show it should work.

Change path in range E1 before running.

OK - I'll have a look. Thanks.

I don't understand what I am supposed to put in cell E1 - what path and why?

The folder is defined here:

Please Login or Register  to view this content.

Very confused.

Anyway, it isn't working in the original file, which I cannot share. I will try to create a mock-up that shows the problem.

I think I can only demonstrate the problem with the original file - it is going to take some time for me to create a copy that I can share.

It won't even work right for one sheet now, let alone two or more!

If I select the first sheet in the user form list, it gets copied into the source file, but then the sheet that gets stripped out is the LAST tab in the workbook, which is then deleted from the workbook. I am really confused.

I am making progress.

With the code below I can produce one or more sheets without sheets being deleted unnecessarily or extraneous sheets being left in the workbook. The only problem remaining is that everything below row 1 is copied from the first sheet in the user form list, not the sheet selected. Can anyone spot why the code is not looping through the sheets?

Please Login or Register  to view this content.

With a lot of trial and error, I have got it working.

The final code is:

Please Login or Register  to view this content.

Thanks for the help!

Hey Ali, sorry I wasn't there for the final stretch but I always work at night so I usually take a nap in the afternoon.

Going over your final code I found the line that was causing everything going sideways.

If you want to put in the time and effort could you please run below code, just for my peace of mind.

Please Login or Register  to view this content.

Thanks, Rudi. I will try it, but can you tell me the line that was screwing everything up? I have spent some time adding some bells and whistles this morning, so I don't want to mess everything up again. I'll try this on a backup copy.

Thanks for looking at it again - I will let you know shortly.

My code now looks like this (I am quite proud of it as a VBA novice):

Please Login or Register  to view this content.

Yes, your new code seems to work. Can you explain what you changed and why?

Copy has two arguments.

Copy Before, After

Somewhere along the way we lost the comma and it looked like this

Please Login or Register  to view this content.

this copied the sheet BEFORE the last sheet making the rest of the code go belly-up.

It should have looked like this

Please Login or Register  to view this content.

copying the sheet AFTER the last sheet so Sheets(Sheets.count) refferred to the copied sheet.

Please Login or Register  to view this content.

Doesn't the original sheet needs to be protected again because now you leave it unprotected ?

Interesting. I will update my new code with this and see if it will execute correctly. Thanks for explaining! I'll let you know how it goes.

Originally Posted by bakerman2

Doesn't the original sheet needs to be protected again because now you leave it unprotected ?

No - it doesn't matter in the original master workbook, and I have left it out deliberately. Only I have access to it. It's the exported sheets that need the protection.

Hi, Rudi!

It doesn't work in my master file. The code stops at this line:

Please Login or Register  to view this content.

Please don't spend any more time on this - the code I ended up with in post #16 does work, and having tested it extensively, I am happy with it. I am not going to fiddle with it any more.

Thanks again for your very valuable insights. Have a nice day!