Create new CSV file with copied data using Excel VBA

Create New CSV file with copied data  using Excel VBA from a macro file.

Besides that,  the data transfer takes place to csv with copy paste program codes. Therefore, the following program generates csv file with copied data.


Sub csv_gen()
Dim wb_main As Workbook
Dim wk_orderform As Worksheet
Dim wb_csv As Workbook
Set wb_main = ActiveWorkbook
Set wk_orderform = wb_main.Sheets("Orders")
Workbooks.Add
Set wb_csv = ActiveWorkbook
Application.DisplayAlerts = False
wb_csv.SaveAs wb_main.Path & "Test.csv", FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = True
With wk_orderform
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
If lastrow > 1 Then 'Expecting lastrow in column A
wk_orderform.Range("A1:F" & lastrow).Copy wb_csv.ActiveSheet.Range("A1")
wb_csv.Activate
Range("A1") = "Product Number"
Range("B1") = "Description"
Range("C1") = "Quantity"
Range("D1") = "Unit Price"
Range("E1") = "Total Price"
wb_main.Activate
Columns("A:E").Select
Selection.Copy
wb_csv.Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Select
Cells.EntireRow.AutoFit
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
wb_csv.Close True
End If
End Sub

You must be logged in to post a comment.