I think this does what you want. It copies each supplier to a different workbook and saves in the C drive:
Sub x()
Dim rng As Range, ws As Worksheet
Application.DisplayAlerts = False
With Sheet1
Sheets.Add().Name = "temp"
.Range("A1", .Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("temp").Range("A1"), Unique:=True
For Each rng In Sheets("temp").Range("A2", Sheets("temp").Range("A2").End(xlDown))
.AutoFilterMode = False
.Range("A1").AutoFilter field:=1, Criteria1:=rng
Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
.AutoFilter.Range.Copy ws.Range("A1")
ws.Name = rng
ws.Move
'Change path below to suit
ActiveWorkbook.Close SaveChanges:=True, Filename:="C:\" & rng & ".xls"
Next rng
.AutoFilterMode = False
Sheets("temp").Delete
End With
Application.DisplayAlerts = True
End Sub
Bookmarks