Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Separating workbook sheets into separate files - quickly!

Posted on 2012-03-30
10
Medium Priority
?
215 Views
Last Modified: 2012-04-20
Hi
If I have a workbook with 20 separately named worksheets - how do I quickly separate these out into separate files in Excel and/or PDF? e.g. if I have a file with 3 worksheets - Jan, Feb and March, how can I quickly separate those into 4 files: original, jan, feb, and March? Either in Excel format or PDF? Thanks.
0
Comment
Question by:agwalsh
10 Comments
 
LVL 11

Accepted Solution

by:
netballi earned 1400 total points
ID: 37786542
0
 
LVL 6

Expert Comment

by:todd_beedy
ID: 37786683
fastest and easiest would be to open the spreadsheet... right click on the sheet and move/copy to new book. rinse and repeat no code needed. You can also use the checkbox copy function to retain data.

As for the PDF, depends on the copy of office you have. you can save to pdf starting in 2007 (once you split the books) or you can "print to pdf" using something like cutepdf, pdf printer, etc etc. Mark the selection entire workbook for single pdf or active sheets for pdf of current sheet only.

That being said... if you have to do this repeatedly... I would suggest writing a small piece of code as suggested as it will be faster over time.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37786788
quote
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:agwalsh
ID: 37788897
@todd_beedy - I'm familiar with that..but the person I am helping has to do it for 25 odd spreadsheets every week so (not surprisingly...) she wants to find a better way..am going to have a look at the code given in the link and try that out.. thanks to everyone..
0
 
LVL 6

Expert Comment

by:todd_beedy
ID: 37788911
if you need help I am fairly good at working with excel and some VBA.

Let me know if I can be of assistance.

The other thing you can try as well is to record a macro and then look at the VBA behind it. you can save it to personal macro workbook so its acrossed all excel books on that machine.
0
 

Author Comment

by:agwalsh
ID: 37799832
Hi
I have tried out the code in the link and it works beautifully :-) thank you.. However while I can get it to work if I create the macro in the workbook - i'm having problems with installing it in the personal macro workbook so that it's available to all files...any suggestions?
Thanks.
0
 
LVL 6

Assisted Solution

by:todd_beedy
todd_beedy earned 600 total points
ID: 37800600
ok the best way to do that is to record a macro into the personal workbook. can be anything...

once you have done that... unhide the personal macros workbook. From there "edit" your macro. You can then import and/or copy and past the code into that module and while inside the VBA.

Once you get the code into your personal workbook... you need hide the personal workbook again and close everything out. Remember to "save" when it asks you if you want to save the personal workbook.

If you have
0
 

Author Comment

by:agwalsh
ID: 37800711
I tried this but had problems..but will try again.. :-)
0
 
LVL 6

Expert Comment

by:todd_beedy
ID: 37801086
ok if you wind up needing help i can provide some assistance over join <dot> me or something similar.
0
 

Author Closing Comment

by:agwalsh
ID: 37870375
Thanks for help to everyone. The person I did it for never got back to me so I reckon the link at the beginning was the best :-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question