VBA needed to separate data into different tabs/worksheets

[XLWiz]

Hello all,

I've been scouring the VBA threads on this forum but have been unsuccessful in finding what I need, thus far.

Working with Excel 2013, I've been trying to develop code that will segregate a massive spreadsheet I report on weekly, into different worksheets/tabs, based on column J and column AK of the file.

Column J has a header I have labeled as "Relationship (No Codes)" with rows of data arranged by "Employee", "Spouse", and "Child". Column AK is labeled as "Benefit Plan Name" and has data arranged by "Dental/Vision Plan", "Plan B: Major Medical Plan", "Plan C: Major Medical Plan" and "Waived Medical Coverage".

What I need, is code that will enable me to automatically separate this data into different tabs containing only Employee Medical, only Employee Dental, only Employee Waived Coverage, and the same arrangements for all other relationships.

Could someone please advise? Thank you very much for your time.

 

[Smitty]

Instead of separating the data, it's usually to keep it all in one place. If you create a pivot table with the fields you want separated in the Report Filter section you can then use the "Show Report Filter Pages" option, which will create a new pivot table worksheet for each item in the list.

This way when you add data to the master sheet all you need to do is refresh the pivot tables.

HTH,

 

[XLWiz]

Instead of separating the data, it's usually to keep it all in one place. If you create a pivot table with the fields you want separated in the Report Filter section you can then use the "Show Report Filter Pages" option, which will create a new pivot table worksheet for each item in the list.

This way when you add data to the master sheet all you need to do is refresh the pivot tables.

HTH,

Hey Smitty,

Thanks for your response. I don't think a pivot table would help me out in this instance, however. After I separate the data into individual worksheets, I use them as lists for a mail merge Word document I produce.

I'll keep your idea in mind for future use, if the report I generate ever turns into a document I simply keep on Excel.

 

[XLWiz]

Bump

 

[XLWiz]

Bump

 

[XLWiz]

??