Segregation of Excel Worksheet into new workbooks

[arviy2k]

Hi,

I have an excel sheet that contains finance information for several cost centers. What I need to do is, filter each cost center, copy the information to a new workbook and save the new workbook. For example, if there are 15 cost centers in my file, I need to filter the data 15 times and copy paste into a new workbook.

Is there any way to automate this using VBA?

What I basically need is individual workbooks for each cost center to be created using the consolidated file. Note, there are multiple rows for each cost center, and cost center names are in column B of the data sheet.

Also, if there is a pivot table that uses my consolidated file for its source, is there a way to copy the pivot table over to the new workbook and use the filtered data as a source?

Thanks for your help.

Regards,
Arvind

 

[VoG]

Try Excel Explosion http://www.datapigtechnologies.com/freeware.htm

 

[arviy2k]

Wow that is exactly what I needed!! Unfortunately my office does not allow the installation of third party add-ins. Is there any way to get that kind of functionality through VBA?

Thank you very much for the excellent suggestion, VoG..

Regards,
Arvind.

 

[VoG]

I posted some code here http://www.mrexcel.com/forum/showthread.php?t=396069

 

[arviy2k]

VoG,

That code is awesome!! Everything I was looking for and much more! Thank you so much.

Regards,
Arvind