Can Macros split up and save documents ?
Posted by Jason on December 30, 2001 5:40 PM
In coloumn A of an Excel Wordbook i have the name of an area, there is 110 different areas but each workbook will only have between 5 and 15 areas.
each area is controlled by an area general manager.
because each workbook has between 500 and 3000 rows of customer information spread over the 5 to 15 areas i am after a macro that will copy only the first row (the coloumn heading) and the rows with the same area name in coloumn A to a new workbook and then save the file in the same folder as the original using the area name as the file name. leaving the original intact and unchanged.
it takes quite a long time to copy the coloumn headings to a new document then all the records with the same area name then save it and continue the process until all areas are done. as i have limited macro knowledge (limited to knowing they save heaps of time) i dont know if macros have the ability to split a document up into several documents and then save the newly created documents.
this is a task i do frequently and assume it can be done quicker, any help would be wonderful
Posted by Douglas on December 31, 2001 4:32 AM
I did something similar by recording a macro. I'm sure it's not the most streamline way - but it works!
You need to create named ranges in your original workbook for each of your regions.
Record a macro which deletes all the other ranges then SAVES AS a new file with the name of the region. Close this and reopen the original, again delete all the regions you don't want and save as a new file. You can copy this bit of code and chage the regions you want to exclude one at a time.
It's a bit cumbersome at first, but you ony need to do it once and then your macro will be available to run anytime. I normally save the macro in a different file, rather than in the original spreadsheet.
Hope this helps!
D
