+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 15 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
    Excel 2007
    Posts
    4,628

    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
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    19,181

    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!
    If anyone has helped you towards solving a problem, then you can thank 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
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    19,181

    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
    Excel 2007
    Posts
    4,628

    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
    217

    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
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    19,181

    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
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    19,181

    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
    217

    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
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    19,181

    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
    Excel 2007
    Posts
    4,628

    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
    Excel 2007
    Posts
    4,628

    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
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    19,181

    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
    Excel 2007
    Posts
    4,628

    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
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    19,181

    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
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    19,181

    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!!!

+ Reply to Thread
Page 1 of 3 1 2 3 LastLast

Thread Information

Users Browsing this Thread

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

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