I have an excel file where each column has varying products codes, descriptions, unit of measure, and 5 different prices (one for each distributor) in this order. Workbook as it is I need to make 5 distinct .csv files, named after each distributor, each with code, discount (normally 0), one of the prices, MU and description, in this order. What one of the CSVs should look like
I can move columns around alright, the issues is that I can't seem to find a way to save the CSVs. The code that I have (not mine) stops as it seems to be "unable to access the .csv" as it tries to save it.
Sub FornitoriToCSV() Const FLDR = "C:\TESTER\Outputs\" 'where to save files Dim rng As Range, wb As Workbook, i As Long, rws As Long Set rng = ActiveSheet.Range("A1").CurrentRegion 'data table rws = rng.Rows.Count 'how many rows of data? For i = 4 To rng.Columns.Count 'loop for each client column (starting at col4) Set wb = Workbooks.Add 'add workbook 'copy data to workbook With wb.Sheets(1) .Range("A1").Resize(rws).Value = rng.Columns(1).Value .Range("B1").Value = "Discount" .Range("B2").Resize(rws - 1).Value = 0 .Range("C1").Resize(rws).Value = rng.Columns(i).Value 'client data .Range("D1").Resize(rws).Value = rng.Columns(3).Value .Range("E1").Resize(rws).Value = rng.Columns(2).Value End With 'save the file using the client name wb.SaveAs Filename:=FLDR & rng.Cells(1, i).Value & ".csv", _ FileFormat:=xlCSVUTF8, CreateBackup:=False wb.Close False Next i End Sub
Any help would be much appreciated!