0

I have a worksheet that looks like this:

Area Contact            COL2
Jon                     test
Jane                    test
Doe                     test
Jon                     test
Doe                     test

What I want to do is write a VBScript which filters the Area Contact column for each of it's options (Jon, Jane, Doe) and saves it as another Excel workbook. So for this particular example, I should end up with 3 workbooks, for each filter option on Jon, Jane and Doe. The part I am struggling with is the filtering part, ensuring it continues to the next filter option. Note that the filter options are not fixed. There could be 2 or 10 different names in the Area Contact column. So each unique Area Contact should have there own file.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Teodorico Levoff
  • 1,641
  • 2
  • 26
  • 44
  • 2
    [AutoFilter Method](https://msdn.microsoft.com/en-us/library/office/aa221844%28v=office.11%29.aspx) –  Aug 10 '15 at 13:07
  • How can AutoFilter be used so I can move on to the next filter option? From what I know AutoFilter can be matched with a specific string, in this case the strings vary. – Teodorico Levoff Aug 10 '15 at 13:20
  • 1
    Play around with Count as a way to figure out if the Area Contact is the first unique value. Then save that unique value as a string and autofilter for it, creating a new workbook with that value as a name and then copying and pasting the visible cells into that workbook. Then go back and clear the filter, and then continue to look for unique values. – KFichter Aug 10 '15 at 16:50
  • 1
    [Extracting the collection of unique values from a filter in VBA](http://stackoverflow.com/questions/31891059/extracting-the-collection-of-unique-values-from-a-filter-in-vba/31891475#31891475) then as @KFichter stated, loop through the filter criteria and create a new workbook for each data set. –  Aug 10 '15 at 20:13

1 Answers1

0

If this were my project I think I would create a separate pivot table that only shows the names (one field from the base data), which would therefore give you your unique values and then build the vba based on a loop going through the pivot table list. When it runs the next time the pivot table will be dynamic and will only show the unique names in your list and you can create the loop based on that.

Jared
  • 48
  • 1
  • 12