VBA Help splitting data based on a ID #

[snb]

This is all you need, using the builtin options in Excel (advancedfilter)

Sub M_snb()
   If Sheets.Count = 1 Then Sheets.Add , Sheets(Sheets.Count)
    
   With Sheet1
     .Columns(1).AdvancedFilter 2, , .Cells(1, 6), True
     sn = .Cells(1, 6).CurrentRegion
     .Cells(1, 6).Offset(2).Resize(UBound(sn)).ClearContents
   
     For j = 2 To UBound(sn)
        .Cells(2, 6) = sn(j, 1)
        Sheet2.UsedRange.ClearContents
        .Cells(1).CurrentRegion.AdvancedFilter 2, .Cells(1, 6).CurrentRegion, Sheet2.Cells(1)
        Sheet2.Copy
        With ActiveWorkbook
           .SaveAs "G:\OF\" & sn(j, 1), 51
           .Close -1
        End With
     Next
   
   .Cells(1, 6).CurrentRegion.ClearContents
  End With
End Sub