+ Reply to Thread
Results 1 to 45 of 45

Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbooks

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

    Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbooks

    What I would really like to be able to do is copy out selected worksheets rather than the whole lot.
    Do you still want to persue this ?

    Select sheets in Listbox and click button.
    Attached Files Attached Files
    Last edited by AliGW; 03-11-2018 at 06:41 AM. Reason: Typo
    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.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Problem with Macro to Split Workbook

    This looks promising, Rudi - thank you! I am not sure quite what I'm looking at - the listbox works fine, but I can't see the split workbook macro in that file at all, so I have no idea what is happening when I click on the save button or where (if anywhere) I should look for the sheets. I am assuming that I need to link this somehow with the macro myself. Could you please advise?
    Last edited by AliGW; 03-08-2018 at 02:16 PM.
    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.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Just tried again and it stops running at this point:
    Please Login or Register  to view this content.

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Ali, put this line
    Please Login or Register  to view this content.
    between this line
    Please Login or Register  to view this content.
    and this one
    Please Login or Register  to view this content.
    What do you get ?

  5. #5
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    271

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Is it another invalid character Ali?

    You've referenced the scripting library via Tools > References and ticking the Microsoft Scripting Runtime Library?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    I get a 'path not found' error.

    There never was and is no invalid character, Dal, and other macros work, but where will I find Tools to check?

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    I could hard code the directory path - that worked before. I'll try it.

    EDIT: Yes - it works if I hard code it.
    Last edited by AliGW; 03-08-2018 at 02:40 PM.

  8. #8
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    271

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Sorry Ali, I thought the previous problem was an invalid character in the worksheet name that was causing the failure of the code.

    In my amateur opinion all that would fail in a path would be a " causing it to jump out of the path/ script unintentionally, or a \ that would cause it to put an incorrect path in this case.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    No - hard coding the same directory path works, so I don't think that's the case.

    I have one further question about customising this script. I would really like for the whole sheet to maintain its formatting, but for the top four rows to copy values and row 5 onwards to copy formulae. There is a button on the sheet that I don't need to copy to the new file (it's just a button with a hyperlink to another page) - can a line be written that ignores it (deosn't copy it across)?

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    I'm off to bed now (have to start working at midnight) but this prevents button to be copied too.
    Please Login or Register  to view this content.

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    How does this work for you ?
    Just replace old code with this one.
    Please Login or Register  to view this content.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    I'd love to try it, Rudi, thanks! However I can't find where you've 'hidden' the code in your test workbook.

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    In VB-Editor select FrmPrint and press F7.

    Don't forget to change "D:\Ali" to correct FolderName.
    Last edited by bakerman2; 03-09-2018 at 02:20 AM.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Thanks - I tweaked the code, but couldn't find where to paste it. Will have a go now!

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Perfect, Rudi! Just one question: is there something I can add that will force the workbook to save as .xlsx instead of .xlsm? Thank you!!!

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Please Login or Register  to view this content.
    Change the 52 to 51, but you must be shure that there's no code in either of the sheets or you'll get error.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Thanks - I will try this ASAP.

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Or if you're not sure use this one.
    Please Login or Register  to view this content.

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Thank you very much once again, Rudi - it works beautifully. I shall see if I can successfully transfer this to the real sheet. Have a lovely evening!

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Glad to help and thanks for rep+. Much appreciated.

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    You deserve it!

    There's just one thing I forgot about: the sheets that are being copied may or may not be protected (no password). How can I get the code to check this, unportect if necessary and then reprotect before exporting the sheet?

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    reprotect before exporting the sheet
    Assuming you meant reprotect AFTER the export. Otherwise move the .Copy after the .Protect
    Please Login or Register  to view this content.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    I meant before saving - I realise now that the sheet has been exported already at this stage. Thank you - I am just about to try this.

    By the way, I had to remove this line yesterday to make the code work:

    Please Login or Register  to view this content.
    Last edited by AliGW; 03-10-2018 at 02:11 AM.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    OK - it unprotects fine, but it doesn't protect again once the contents have been copied. I've tried this:

    Please Login or Register  to view this content.
    and this:
    Please Login or Register  to view this content.

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    We're getting a bit lost in translation I'm afraid.
    When the sheet in your masterfile is protected, it gets unprotected, copied and protected again. That bit works fine for me
    The newly exported sheet however is unprotected. Is it that one that needs to be protected also ?

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Ah - I see what you mean. Yes, that's it - sorry, I realise now that I wasn't quite clear.

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Please Login or Register  to view this content.
    Last edited by bakerman2; 03-10-2018 at 04:11 AM.

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Genius!

    Thank you, Rudi - I'm dead chuffed.

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    I used the code you provided before editing it - is there something I need to be aware of here?

  30. #30
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    I'm afraid I've hit another snag. The code works fine as long as the workseet names are single words. As soon as there are two (e.g. Happy Days), the code fails with a "save as of object _Workbook" failure message. This line of the code is flagged in the debugger:
    Please Login or Register  to view this content.
    I have some sheets with multiple words and a few with the ampersand (&) character in their name. I don't want to have to change these. Is there a tweak for this, please?

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    The spaces shouldn't matter in constructing the filename (just tested it and no prob)
    As far as other illegal char put the function just below current userform code.
    Please Login or Register  to view this content.
    and change this line
    Please Login or Register  to view this content.
    to this
    Please Login or Register  to view this content.
    I've used a dash but you can change that to any another to your liking. Either way illegal char need to be replaced to construct proper filename.

  32. #32
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    Thanks, Rudi. I will try this, but I am a bit perplexed: if I use the KuTools workbook splitter (which I can't use at work), it copies out the worksheets fine, whether there are spaces or ampersands in the name or not, so there must be a way.

    Thanks for all your help. I'll let you know how I get on.

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

    Re: Selecting Worksheets to Copy Out to New Workbooks

    I've just tested it with spaces, & and @ in the sheetname and I also didn't have any problem.

  34. #34
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Selecting Worksheets to Copy Out to New Workbooks

    I thought it was because I am saving to a OneDrive folder, but because KuTools has no problem with the same workbook, it can't be that. There must be something in the latest KuTools code that is different to the old code on the ExtendOffice site (from 2014), which I originally used. I have scoured the Internet and cannot find any solution for this error message that works for me. It isn't the file path name length getting too long - I've checked that, and it's well under the maximum character count allowed. It's really frustrating!

    What I DO know, from trial and error, is that it is spaces in the worksheet names that are causing this.
    Last edited by AliGW; 03-11-2018 at 06:24 AM.

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

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    Do you have an example of such a name because like I said earlier tried worksheet name with spaces and it didn't give a problem on my end.

  36. #36
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    Yes - they are really simple! Here are two:

    Design Technology
    Business & Economics

    Bear in mind that I am using Office 365.

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

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    Ali, I haven't found anything that would explain such behaviour so far.
    Since I don't share your configuration I can't test so I'm a bit on a limb with this one.

  38. #38
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    Thanks anyway, Rudi. It's an irritation, but I'll have to find a workaround (renaming tabs if necessary). Thanks again for all your help. I won't mark the thread as solved in case anyone else has any ideas.

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

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    You could use this for the time being
    Please Login or Register  to view this content.
    and when you get a permanent solution you could mass rename all files in folder with "-" in it.

  40. #40
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    Thanks Rudi - I'll try that.

  41. #41
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    Right, so it still fails at this line, which is AFTER the file naming line:

    Please Login or Register  to view this content.

  42. #42
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    Does this help?
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  43. #43
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    OK - after a lot of trial and error, and depite the path name being well below what Windows claims to be able to handle, it was in fact that, and NOT the spaces in the worksheet names, that was preventing this from working. I have changed the hard-coded path to one higher up the hierarchy and now all is well.

    Thank you so much once again for all your efforts.
    Last edited by AliGW; 03-12-2018 at 05:21 AM.

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

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    Glad you sorted it out.

  45. #45
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,332

    Re: Issue with Spaces in Worksheet Names if Selecting Worksheets to Copy Out to New Workbo

    So am I. It was beginning to do my head in!!!

+ 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. Copy Worksheets into already existing Workbooks with VBA
    By mr.sleepy95 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2018, 05:43 PM
  2. [SOLVED] copy worksheets from a couple of workbooks to other workbooks
    By masben in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2014, 02:56 PM
  3. Copy selected worksheets between workbooks onto named worksheets
    By AndrewSimpson87 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-24-2014, 03:25 PM
  4. Copy/Paste Range of Data from Multiple Workbooks/Worksheets to Master Workbook/Worksheets
    By NumberCruncher311 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-19-2013, 08:21 PM
  5. Macro - user selecting certain worksheets to copy
    By cja24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2012, 03:13 AM
  6. Userform for Selecting Open Workbooks - Copy Selections
    By muckem333 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-08-2011, 02:19 PM
  7. copy worksheets to new workbooks
    By CThai in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2007, 05:40 PM

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