How to create in VBA array with name of sheets that are cell values and move data accordingly

[ArtyS20]

I have sales representatives data in a workbook in a "Summary" tab. Each rep has a row of data. I want each rep to have its own sheet. Each sheet will only show data for each specific person and name the sheet as the person. Number of reps varies. At the end i want to have "Summary" tab with all data, and "Rep1", "Rep2", etc. with only their own data.

Thanks

 

[mumps]

In which column are the rep names? Does each rep have more than one row of data?

 

[ArtyS20]

In which column are the rep names? Does each rep have more than one row of data?

Column G and each rep will only have one row. Number or reps varies all the time though

Thanks

 

[mumps]

Try:

Sub createSheets()
    Application.ScreenUpdating = False
    Dim rep As Range
    For Each rep In Range("G2", Range("G" & Rows.Count).End(xlUp))
        rep.EntireRow.Copy
        If Not Evaluate("isref('" & rep.Value & "'!A1)") Then
          Sheets.Add(After:=Sheets(Sheets.Count)).Name = rep
          Range("A1").PasteSpecial
       End If
    Next rep
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[ArtyS20]

Try:

Sub createSheets()
    Application.ScreenUpdating = False
    Dim rep As Range
    For Each rep In Range("G2", Range("G" & Rows.Count).End(xlUp))
        rep.EntireRow.Copy
        If Not Evaluate("isref('" & rep.Value & "'!A1)") Then
          Sheets.Add(After:=Sheets(Sheets.Count)).Name = rep
          Range("A1").PasteSpecial
       End If
    Next rep
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Perfect, thanks much!

 

[mumps]

You are very welcome.