Split worksheet into many based on Cell

[Brian Nordberg]

I have a excel doc with an Department ID field that breaks
down employees for that department. I need to
programatically make a new document for each occurrence of
the department within the doc.
For example
DeptID Person
1 Brian
1 Rob
2 John
2 Mary
3 Colleen

would be broken into 3 new files that contain only 1
departments worth of data.
Ideas?
Thanks

 

[Tushar Mehta]

[This followup was posted to microsoft.public.excel with an email copy
to Brian Nordberg.
Please use the newsgroup for further discussion.]

To separate data based on the department ID into worksheets (not
workbooks), create a PivotTable with the dept ID as the Page field.
One of the options XL offers is to split the data for the table into
separate worksheets based on the Page Field value.

Then, save each worksheet as a separate workbook.

--
Trouble finding replies to your posts? Use a newsreader. See the
tutorial 'Outlook Express and Newsgroups' on my web site

Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Brian Nordberg]

I should expound.
I will have over 330 department ID's so I have to do it
programatically or I will be doing it all year.
Ideas?

 

[Tushar Mehta]

Record a macro doing what I suggested, but save just one worksheet.
Then, share that here and someone should be able to generalize the
macro to an arbitrary number of worksheets.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Dave Peterson]

You might be able to steal some code from Debra Dalgleish's sample workbook at:

http://www.contextures.com/excelfiles.html
Look for:
Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb