+ Reply to Thread
Results 1 to 22 of 22

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

  1. #1
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

    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.
    Last edited by AliGW; 03-02-2019 at 05:45 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,881

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

    Posted answer in previous thread.

    My bad.

    Put this part

    Please Login or Register  to view this content.
    just above next.
    Last edited by AliGW; 03-02-2019 at 05:52 AM.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    I'll try it - thanks!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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!

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,881

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

    OK.

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

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,881

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

    Damn. Should slow myself down and look better. Sorry.
    Please Login or Register  to view this content.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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!

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,881

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

    Made a dummy file to show it should work.

    Change path in range E1 before running.
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    OK - I'll have a look. Thanks.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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.
    Last edited by AliGW; 03-02-2019 at 09:51 AM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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.
    Last edited by AliGW; 03-02-2019 at 12:16 PM.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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.
    Last edited by AliGW; 03-02-2019 at 01:33 PM.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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!
    Last edited by AliGW; 03-02-2019 at 02:32 PM.

  15. #15
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,881

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

    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.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

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

  18. #18
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,881

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

    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.

  19. #19
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,881

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

    Please Login or Register  to view this content.
    Doesn't the original sheet needs to be protected again because now you leave it unprotected ?

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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.

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    Quote Originally Posted by bakerman2 View Post
    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.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,694

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

    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!
    Last edited by AliGW; 03-03-2019 at 06:05 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA to Split Workbook: No Longer Converting Formula Results to Values
    By AliGW in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 03-02-2019, 05:46 AM
  2. [SOLVED] Split macro no longer working
    By PandoraGirl in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-07-2019, 05:33 PM
  3. [SOLVED] Multi Column List Box (14) in user form not working
    By Pradip2 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 02-06-2019, 03:12 AM
  4. How user Form can be accessed by multi user in online?
    By bala04msw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2018, 09:47 PM
  5. Copied working macro to new workbook, but no longer working
    By twisted31 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2017, 06:17 PM
  6. User added buttons no longer working
    By capt_tony in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2013, 11:42 AM
  7. How do I get value of a multi-selection form listbox?
    By foxguy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-15-2010, 03:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1