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