Saving Excel file as CSV while excluding header in VBA?

Question

I have written the following code:

Sub create_talpo_kiint_csv()
    Dim FileName As String
    Dim PathName As String
    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets("SheetName").range("B2", range("B2").End(xlToRight).End(xlDown)).Select
    FileName = "test.csv"
    PathName = Application.ActiveWorkbook.Path
    ws.Copy
    ActiveWorkbook.SaveAs FileName:=PathName & "\" & FileName, _
        FileFormat:=xlCSV, CreateBackup:=False
End Sub

The goal is to save the excel file as csv while excluding the header. However, the code above is not working. What should I do to fix it?

Answer 1

Note that ws.Copy copies the entire worksheet regardles your selection. So I recommend to delete the header after the copy.

ws.Copy
ActiveWorkbook.Worksheets(1).Rows(1).Delete Shift:=xlUp  ' delete first row

Make sure you set ws just to the worksheet then

Set ws = ActiveWorkbook.Worksheets("SheetName")