VBA to create new tab based on each change in....

[bigdataguy]

Hello all-

I have a VBA question. I have a large data set and want to create a new tab based on each change in salesperson.


My data range is A3 to BN3000, with the headers in row 2. Salesperson is in column G, starting at G3. What I want to do is create a new tab at each change in salesperson, and to move the applicable data for that salesperson over to the new tab as well.

Is there a quick line of code that can do this? Thank you, I appreciate the help!

 

[My Aswer Is This]

I need a more complete explanation.
You said:

I have a VBA question. I have a large data set and want to create a new tab based on each change in salesperson.

Give me a example.

If you enter "Alpha" in Range("G12") you want to created a new sheet named "Alpha"
And that row copied to sheet named "Alpha"

Is that what your saying?
And the new sheet named "Alpha" will only have one row of data?


​

 

[Trixterz]

You can easily do this using pivotTable without having to write a VBA script.

 

[Peter_SSs]

You can easily do this using pivotTable without having to write a VBA script.

Hmm, how can a pivot table create a new sheet for each salesperson & move their data to that sheet?
@bigdataguy
Welcome to the MrExcel board!

Try this in a copy of your workbook. It assumes that the "large data set" is on the active sheet when the code is run.

Sub MoveDataToNewSheets()
  Dim ActSh As Worksheet
  Dim a As Variant
  Dim i As Long
  
  Set ActSh = ActiveSheet
  a = Range("G2", Range("G" & Rows.Count).End(xlUp)).Value
  With ActSh.UsedRange.Offset(1)
    For i = 2 To UBound(a)
      If a(i, 1) <> a(i - 1, 1) Then
        Sheets.Add After:=Sheets(Sheets.Count)
        On Error Resume Next
        Sheets(Sheets.Count).Name = a(i, 1)
        On Error GoTo 0
        .AutoFilter Field:=7, Criteria1:=a(i, 1)
        .Copy Destination:=Sheets(Sheets.Count).Range("A1")
      End If
    Next i
    .Parent.AutoFilterMode = False
  End With
End Sub

 

[bigdataguy]

I need a more complete explanation.
You said:

I have a VBA question. I have a large data set and want to create a new tab based on each change in salesperson.

Give me a example.

If you enter "Alpha" in Range("G12") you want to created a new sheet named "Alpha"
And that row copied to sheet named "Alpha"

Is that what your saying?
And the new sheet named "Alpha" will only have one row of data?


​

Thanks for the reply. Yes, that is what I'm saying. However, Alpha can have any number of rows, but salesperson is sorted by alphabetically already, so the script would have to account for each change in salesperson

 

[bigdataguy]

not sure you can create new tabs based on a pivot

 

[bigdataguy]

Hmm, how can a pivot table create a new sheet for each salesperson & move their data to that sheet?

@bigdataguy
Welcome to the MrExcel board!

Try this in a copy of your workbook. It assumes that the "large data set" is on the active sheet when the code is run.

Sub MoveDataToNewSheets()
  Dim ActSh As Worksheet
  Dim a As Variant
  Dim i As Long
  
  Set ActSh = ActiveSheet
  a = Range("G2", Range("G" & Rows.Count).End(xlUp)).Value
  With ActSh.UsedRange.Offset(1)
    For i = 2 To UBound(a)
      If a(i, 1) <> a(i - 1, 1) Then
        Sheets.Add After:=Sheets(Sheets.Count)
        On Error Resume Next
        Sheets(Sheets.Count).Name = a(i, 1)
        On Error GoTo 0
        .AutoFilter Field:=7, Criteria1:=a(i, 1)
        .Copy Destination:=Sheets(Sheets.Count).Range("A1")
      End If
    Next i
    .Parent.AutoFilterMode = False
  End With
End Sub

Worked perfectly!!! Thank you so much, I really appreciate the help.

Now building off of this, if I wanted to automatically send out an email blast to each salesperson, with only their tab of data, is there a quick line of code that would allow me to do that? Assuming that the reference master email list is on a tab called 'email list' and ranges from from A2 to Z2. This would be a major time saver because there are about 50 salespeople that need to be emailed through outlook with only their data.

 

[Peter_SSs]

Worked perfectly!!! Thank you so much, I really appreciate the help.

You are very welcome.

Now building off of this, if I wanted to automatically send out an email blast to each salesperson, with only their tab of data, is there a quick line of code that would allow me to do that? Assuming that the reference master email list is on a tab called 'email list' and ranges from from A2 to Z2. This would be a major time saver because there are about 50 salespeople that need to be emailed through outlook with only their data.

That is not a strength of mine but similar questions have been asked in this forum before so you could search for those and you should find some very useful information here.

 

[bigdataguy]

You are very welcome.


That is not a strength of mine but similar questions have been asked in this forum before so you could search for those and you should find some very useful information here.

Thank you sir!