Try this macro. Replace "Your Username" (in red) with your computer username.
Sub CreateShippingRecords()
Application.ScreenUpdating = False
Dim LastRow As Long, Rng As Range, RngList As Object, srcWS As Worksheet, srcWB As Workbook, item As Variant
Set srcWB = ThisWorkbook
Set srcWS = ThisWorkbook.Sheets("record")
LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set RngList = CreateObject("Scripting.Dictionary")
For Each Rng In srcWS.Range("B2", srcWS.Range("B" & srcWS.Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
RngList.Add Rng.Value, Nothing
End If
Next
For Each item In RngList
With srcWS.Range("A1").CurrentRegion
.AutoFilter 2, item
.SpecialCells(xlCellTypeVisible).Copy
Workbooks.Add
Sheets("Sheet1").Name = "record"
Sheets("record").PasteSpecial
.AutoFilter
srcWB.Sheets("LME").Copy after:=Sheets(1)
ActiveWorkbook.SaveAs Filename:="C:\Users\Your Username\Documents\" & item & " - Shipping Record.xlsx", FileFormat:=51
ActiveWorkbook.Close False
End With
Next item
Application.ScreenUpdating = True
End Sub
Bookmarks