Try
Sub test()
Dim a, i As Long, ws As Worksheet
Application.ScreenUpdating = False
a = Workbooks("sample data.xlsx").Sheets("sheet1").Cells(1).CurrentRegion.Value
Set ws = ThisWorkbook.Sheets.Add
ThisWorkbook.Sheets("commodity_template (3)").Cells(1).CurrentRegion.Copy
ws.Cells(1).PasteSpecial xlPasteValues
ws.Columns("a:b").Replace a(2, 3), Chr(2), 2
ws.Columns("n").Replace a(2, 2), Chr(2), 2
For i = 2 To UBound(a, 1)
ws.Columns("a:b").Replace Chr(2), a(i, 3), 2
ws.Columns("n").Replace Chr(2), a(i, 2), 2
ws.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & a(i, 2) & ".csv", xlCSV
ActiveWorkbook.Close False
ws.Columns("a:b").Replace a(i, 3), Chr(2), 2
ws.Columns("n").Replace a(i, 2), Chr(2), 2
Next
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Bookmarks