Code to move data from master worksheet to subsequest worksheets

[Brad Rego]

Hello! New to macros/vba. Looking for help with code that can move data from worksheet 1 (the master worksheet) to subsequent worksheets based on each change in column F (Product Vendor).
I would like a new worksheet created for each unique value in column F along with all the row data that accompanies it.

If the example below were worksheet 1 (the master tab) then I would want 3 additional worksheets created. One for Symetra FA, one for AXA and one for Prudential along with all the corresponding row data. Understand that there could be hundreds of rows on the master tab with up to 30 unique Product Vendors in column F.

Any help is greatly appreciated!


Prior NamePrior StatusNew NameEmpl NumberProductTypeProductVendorContract Number
HOUSEINACTIVEJoe Smith111111FIXASymetra FA123
HOUSEINACTIVEJoe Smith111111VAAXA123
HOUSEINACTIVEJoe Smith111111VAPrudential123

 

[p45cal]

A totally empty workbook is not a lot of use.

 

[Brad Rego]

Apologies for the blank file. Here is a populated one.

 

[p45cal]

To get you started, in attached the following code and a button to run it:

Sub blah()
With Sheets("Sheet1")
  For Each vendor In Members(.Range("A1").CurrentRegion.Columns(6).Offset(1))
    Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
    NewSht.Name = vendor
    .Range("A1").AutoFilter Field:=6, Criteria1:=vendor
    .AutoFilter.Range.Copy NewSht.Range("A1")
  Next vendor
  .Range("A1").AutoFilter
End With
End Sub

Function Members(TheRng)
Dim TheMembers As Object, IsPresent As Boolean
Set TheMembers = CreateObject("Scripting.Dictionary")
TheMembers.comparemode = TextCompare
For Each cll In TheRng.Cells
    If Not IsEmpty(cll) Then
      If Not TheMembers.exists(cll.Value) Then TheMembers.Add cll.Value, cll.Value
    End If
Next cll
Members = TheMembers.items
End Function

 

[Brad Rego]

This is great! It worked just as I needed it. One question...if columns are added or deleted and the 'trigger column' gets moved from column F, would I simply adjust the code so that the number "6" (for "F") gets changed to the appropriate column number? Thank you.

 

[p45cal]

Yes.. all the 6s.