Insert Worksheet(s) based on data - Updated

Good Morning,

I am trying to figure out a way to use VBA to insert tabs (Worksheets) into the Workbook based upon the criteria in column D. As an example i have an account report that i need to distribute to the different account reps. Tday this is done through cut and paste. In column D i have the account names, assume: Larry, Moe, Curly, Shemp. I would like to be able to automatically move their respective data into a seperate tab. Then I would simply email the info to the individuals while i keep the master with all the info.

The daily dataset will vary in length each day. todays as an example contains 54 rows and there are nine (9) account reps that i want to create separate tabs for. I have made some significant progress but am caught on something... the code i am using to rename the tabs works the first time and after that it bombs out because it is trying to rename a tab the same as the previous.

Not sure about protocol but i am pasting in the code below, some of it is commented out while i work on it.

Sub CreatePivotTable()
'Application.ScreenUpdating = False

Dim fname As String
'fname = "\\fileservices\files\operations\share\aged_fails\"
'ChDir "\\fileservices\files\operations\share\aged_fails\"
fname = "\\filesrv2\operations\gscc\voice_uncompared\"
myfield = Selection ' the column heading of the field to split
myfield2 = Selection.Offset(1, 0)
ActiveSheet.PivotTableWizard
With ActiveSheet.PivotTables(1)
  .AddFields myfield
  .PivotFields(myfield).Orientation = xlDataField
  .ColumnGrand = False
  For Each cell In .DataBodyRange
    cell.ShowDetail = True
    ActiveSheet.Name = myfield2
'    ActiveWorkbook.SaveAs fname & cell.Offset(, -1)
   
  Next
  Application.DisplayAlerts = False
  .Parent.Delete
  Application.DisplayAlerts = True
End With
Application.ScreenUpdating = True

End Sub

Any help you could offer would be greatly appreciated.

Many thanks.

Dave

Are you sure you have copied the cot code, that is code to create a pivot table.

Hello Bob, thank you for taking the time to respond. Yes this is the correct code, i use the pivot table to create the new tab/worksheet. The problem (which i just overcame) was where the code 'named' the worksheet; it would succesfully and correctly name the first sheet but it would try to apply the same name to the subsequent sheets and therefore fail. i commented out the "myfield2" and changed the activesheet line as follows:

ActiveSheet.Name = cell.offset(,-1)

That does the trick.

However now i am trying to delete every row where there is a certain string "UNC". I cant get the code right to handle varying size of the rows (today there are 54 tomorrow it could be 587, and tomorrow zero.

What i need to do is if: there are zero rows, exit and if there are any rows, look at each one and if they contain "UNC" delete the row.

Thanks again for your assistance, i will keep plugging away.

Thank you.

Dave