PDA

View Full Version : [SOLVED] Copy Data from one sheet to New Workbook with the New Header Name



JOEYSCLEE
02-13-2017, 08:19 AM
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.:help

JBeaucaire
02-13-2017, 01:22 PM
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
02-13-2017, 08:24 PM
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
02-13-2017, 08:40 PM
1) Try pasting the data starting in row2 like the original example.
2) No idea yet

JOEYSCLEE
02-14-2017, 08:49 AM
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
02-14-2017, 08:57 AM
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
02-14-2017, 06:23 PM
Thanks Jerry for reviewing!! It works perfectly after switching column "A" to "C" as per your listed code.:clap:

JBeaucaire
02-16-2017, 09:20 AM
Excellent! Glad it's working.