Macro to copy rows to new workbook & separate worksheets

[malachi]

Hello

Can anyone help me with a macro for the following problem.

I have a master sheet with hundreds of rows. When the category in column A (department name) changes, I want to export these rows into a new workbook which will be called after that department.

eg. SALES.XLS

I have seen some great posts already on this site which shows me how to do this.(e.g. http://www.excelforum.com/excel-prog...e-in-data.html)

However I have another requirement which complicates this.

There is another column in the master sheet which lists dates in dd/mm/yy format. Once the rows are exported to the new workbooks, I need these rows to further separate into four different worksheets, one for each quarter.

Q1,Q2,Q3,Q4

There are perhaps a dozen differnt departments in the master sheet (HR, IT, Sales, etc)


Once the macro is run, the master sheet should still exist, but now there will be 12 new workbooks named after the department they contain, all with four worksheets, one for each quarter.

Finally, if possible, they should all be saved to a new location within a folder called after the department

e.g. S:\\MIS data\Sales\Sales.xls

Any help would be much appreciated.

Martin

[Leith Ross]

Hello malachi,

Welcome to the Forum!

It would be a big help if you could two things: Post a copy of the master workbook and define the dates or months for each quarter. Once you have done that, it will be easier to provide you with a solution.

[JBeaucaire]

'SHEET1 TO MANY WORKBOOKS

I have a macro that may be "ready to use" for parsing rows of data from one sheet to many workbooks named for the same values.

Sheet1 to Many Workbooks

I've edited the SAVE part to use your naming convention, just make sure those directories already exist and they will save into there. The original code on the website added today's date to the named file, you can add that back in if you wish.


Since this macro is good for getting the one worksheet out to many workbooks, I wrote a small routine that would then split out that new workbook to multiple worksheets, it's called ParseQuarters.

Here are the two macros:

Please Login or Register  to view this content.

[malachi]

Leith - thank you for the welcome and the guidelines. I will follow these in future.

Jerry - thanks a million for posting the code. I look forward to trying this out. Really appreciate the help.

[malachi]

Hi Jerry,

I tried the macro you posted without success (due to my own lack of experience with VB). I get a Type Mismatch error, though I suspect it's just a small adjustment that is required. I attach a copy of sample data - would you be able to have a look?

I've named the worksheet "MIS" and I've updated the code to reflect this. Also, I've updated the code to send the new workbooks & worksheets to the destination H:\2010\
(This folder does exist).

Can you advise what the problem is?

Thanks again.

Martin

[JBeaucaire]

You didn't indicate to me where the type mismatch occurred for you, and when I ran the main macro it worked OK.

The second macro you failed to edit the DateCol variable at the top to the proper column with dates for the parsing.

I decided to restructure the new macro a little so you send the DateCol a value in the CALL command from the main macro, that should make it a little more obvious what is needed. I also corrected some filtering errors I spotted in the second macro now that I had an actual dataset to test it on.

Please remove all code from the ThisWorkbook module. These macros do not belong in there.

Click on Insert > Module and put these into the standard module that appears.

Please Login or Register  to view this content.

[malachi]

Thank you again. I see what you mean about the Datecol value. I'm slowly getting to grips with it.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.