Copy Data from one sheet to New Workbook with the New Header Name

[JOEYSCLEE]

Hi, there
Would you please help to write the macro for those data from sheet "Data" to the New File with the New Header Name? Regarding File Format of the New File, it should be "CSV (Comma delimited) and the file name should be same as the Original File Name.

[JBeaucaire]

This macro seems to do what you've outlined:

Option Explicit

Sub OutputCSV()
Dim LR As Long, wsOUT As Worksheet, wsData As Worksheet
Dim fPATH As String, fNAME As String

fPATH = "C:\TEMP\"  'remember the final \ in this folder string
fNAME = ActiveWorkbook.Name
fNAME = Left(fNAME, InStrRev(fNAME, ".")) & "csv"

Set wsData = ActiveSheet
Set wsOUT = Sheets.Add

With wsOUT
    .Range("A1:I1").Value = [{"Style","Color","Path Name","Vendor # for PO","TOTAL FOB","Jesta Routing","Ship Mode","DC","Default(Y/N)"}]
    LR = wsData.Range("A" & Rows.Count).End(xlUp).Row
    .Range("A2:A" & LR).Formula = "='" & wsData.Name & "'!D2"
    .Range("B2:B" & LR).Formula = "='" & wsData.Name & "'!E2"
    .Range("C2:C" & LR).Formula = "='" & wsData.Name & "'!G2"
    .Range("D2:D" & LR).Formula = "=LEFT(C2,5)"
    .Range("F2:F" & LR).Formula = "='" & wsData.Name & "'!J2"
    .Range("G2:G" & LR).Formula = "='" & wsData.Name & "'!K2"
    .Range("H2:H" & LR).Formula = "=RIGHT(C2,2)"
    .Range("I2:I" & LR).Formula = "Y"
    
    .Move
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=fPATH & fNAME, FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close False
End With

End Sub

[JOEYSCLEE]

Hello, Jerry
Thanks for your quick response! It works for the example. Nevertheless, when I put the code in my actual file and change the cells as picture shown, it did not work.
Enclosed the pictures for your reviewing.

[JBeaucaire]

1) Try pasting the data starting in row2 like the original example.
2) No idea yet

[JOEYSCLEE]

Actual, the data is starting in row2 like the original example. Enclosed the Testing file with the code (i.e. I removed the File Path for saving CSV format) for your reference. There is no Header & only 2 rows after running the code.

[JBeaucaire]

This is the issue, then, column A can't be used to find the last row of data (LR). Switch to "C".

        LR = wsData.Range("C" & Rows.Count).End(xlUp).Row

[JOEYSCLEE]

Thanks Jerry for reviewing!! It works perfectly after switching column "A" to "C" as per your listed code.

[JBeaucaire]

Excellent! Glad it's working.