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

[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

In article <00eb01c381e2$aa2d66c0$(E-Mail Removed)>,
(E-Mail Removed) says...
> 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
>
>

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?

>-----Original Message-----
>
>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
>
>In article <00eb01c381e2$aa2d66c0$(E-Mail Removed)>,
>(E-Mail Removed) says...
>> 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
>>
>>
>.
>

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

In article <c85401c38211$277c2c50$(E-Mail Removed)>,
(E-Mail Removed) says...
> 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?
>
> >-----Original Message-----
> >
> >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
> >
> >In article <00eb01c381e2$aa2d66c0$(E-Mail Removed)>,
> >(E-Mail Removed) says...
> >> 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
> >>
> >>
> >.
> >
>

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

Brian Nordberg wrote:
>
> 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

--

Dave Peterson
(E-Mail Removed)