Do you still want to persue this ?What I would really like to be able to do is copy out selected worksheets rather than the whole lot.
Select sheets in Listbox and click button.
Do you still want to persue this ?What I would really like to be able to do is copy out selected worksheets rather than the whole lot.
Select sheets in Listbox and click button.
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.
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!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Just tried again and it stops running at this point:
Please Login or Register to view this content.
Ali, put this linebetween this linePlease Login or Register to view this content.
and this onePlease Login or Register to view this content.
What do you get ?Please Login or Register to view this content.
Is it another invalid character Ali?
You've referenced the scripting library via Tools > References and ticking the Microsoft Scripting Runtime Library?
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?
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.
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.
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)?
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.
How does this work for you ?
Just replace old code with this one.
Please Login or Register to view this content.
I'd love to try it, Rudi, thanks! However I can't find where you've 'hidden' the code in your test workbook.
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.
Thanks - I tweaked the code, but couldn't find where to paste it. Will have a go now!
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!!!
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.Please Login or Register to view this content.
Thanks - I will try this ASAP.
Or if you're not sure use this one.
Please Login or Register to view this content.
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!
Glad to help and thanks for rep+. Much appreciated.
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?
Assuming you meant reprotect AFTER the export. Otherwise move the .Copy after the .Protectreprotect before exporting the sheet
Please Login or Register to view this content.
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.
OK - it unprotects fine, but it doesn't protect again once the contents have been copied. I've tried this:
and this:Please Login or Register to view this content.
Please Login or Register to view this content.
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 ?
Ah - I see what you mean. Yes, that's it - sorry, I realise now that I wasn't quite clear.
Please Login or Register to view this content.
Last edited by bakerman2; 03-10-2018 at 04:11 AM.
Genius!
Thank you, Rudi - I'm dead chuffed.
I used the code you provided before editing it - is there something I need to be aware of here?
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:
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?Please Login or Register to view this content.
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.
and change this linePlease Login or Register to view this content.
to thisPlease 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.Please Login or Register to view this content.
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.
I've just tested it with spaces, & and @ in the sheetname and I also didn't have any problem.
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.
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.
Yes - they are really simple! Here are two:
Design Technology
Business & Economics
Bear in mind that I am using Office 365.
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.
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.
You could use this for the time beingand when you get a permanent solution you could mass rename all files in folder with "-" in it.Please Login or Register to view this content.
Thanks Rudi - I'll try that.
Right, so it still fails at this line, which is AFTER the file naming line:
Please Login or Register to view this content.
Does this help?
Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
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.
Glad you sorted it out.
So am I. It was beginning to do my head in!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks