Share via

Excel VBA - Delete entire row before saving to CSV

Anonymous
2020-06-29T16:05:15+00:00

Dear Community members,

I use below code to export .xlsm worksheets to .csv

Can somebody tell me how to remove the entire first row in worksheet (sheet1) before it is exported to CSV?

Public Sub SaveWorksheetsAsCsv()

Dim WS As Worksheet

Dim newCSV As Workbook

Dim SaveToDirectory As String

' Store current details for the workbook

SaveToDirectory = "W:\public\DELMIAPartnerPortfolioReportGEO"

Application.DisplayAlerts = False

Application.DisplayAlerts = False

      For Each WS In ThisWorkbook.Worksheets

            Set newCSV = Application.Workbooks.Add

                  WS.UsedRange.Copy newCSV.Sheets(1).Range("A1")

                  newCSV.SaveAs Filename:=SaveToDirectory & WS.Name & ".csv", _

                  FileFormat:=xlCSVMSDOS, CreateBackup:=False

                  newCSV.Close

      Next WS

      Application.DisplayAlerts = False

      Application.DisplayAlerts = True

End Sub

Thanks a lot for your help!

Kind regards,

Erik

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-29T16:46:56+00:00

    Well,...   if you will remove the first row of data on the CSV sheet

    there is no point to copy the entire used range data from the active sheet

    This line WS.UsedRange.Offset(1).Resize(WS.UsedRange.Rows.Count - 1).Copy

    will leave out the first row of the data to be copied, before paste it to the newCSV sheet (.csv file)

    So, the code 

    ************************************************************************************************

    Public Sub SaveWorksheetsAsCsv()

    Dim WS As Worksheet

    Dim newCSV As Workbook

    Dim SaveToDirectory As String

    ' Store current details for the workbook

    SaveToDirectory = "W:\public\DELMIAPartnerPortfolioReportGEO"

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

          For Each WS In ThisWorkbook.Worksheets

                Set newCSV = Application.Workbooks.Add

                      WS.UsedRange.Offset(1).Resize(WS.UsedRange.Rows.Count - 1).Copy newCSV.Sheets(1).Range("A1")

                      newCSV.SaveAs Filename:=SaveToDirectory & WS.Name & ".csv", _

                      FileFormat:=xlCSVMSDOS, CreateBackup:=False

                      newCSV.Close

          Next WS

    Application.ScreenUpdating = True

    Application.DisplayAlerts = True

    End Sub

    **************************************************************************************************

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-06-29T16:44:52+00:00

    When you copy a worksheet but neglect to supply a location, a new workbook with the copy as its single worksheet is created. The new workbook is also the ActiveWorkbook at the time of its creation.

    Option Explicit

    Public Sub SaveWorksheetsAsCsv()

        Dim ws As Worksheet, SaveToDirectory As String

        ' Store current details for the workbook

        SaveToDirectory = "W:\public\DELMIAPartnerPortfolioReportGEO"

        'prevent overwrite warnings

        Application.DisplayAlerts = False

        For Each ws In ThisWorkbook.Worksheets

            'create a copy in a new active workbook

            ws.Copy

            With ActiveWorkbook

                .Sheets(1).Cells(1).EntireRow.Delete shift:=xlUp

                'whenever possible let the FileFormat dictate the file extension

                .SaveAs Filename:=SaveToDirectory & .Sheets(1).Name, FileFormat:=xlCSVMSDOS, CreateBackup:=False

                '.SaveAs Filename:=SaveToDirectory & .Sheets(1).Name & ".csv", FileFormat:=xlCSVMSDOS, CreateBackup:=False

                .Close SaveChanges:=True

            End With

          Next ws

          Application.DisplayAlerts = True

    End Sub

    0 comments
  3. Anonymous
    2020-06-29T16:31:57+00:00

    Use,

    newCSV.Range("1:1").EntireRow.Delete Shift:=xlUp

    0 comments
  4. Anonymous
    2020-06-29T16:27:37+00:00

    Hi,

    I get a run time error. 

    Run-time error '438':

    Object doesn't support this property or method

    Are you familiar with the run-time error?

    I placed the code as I wrote it below.

    Public Sub SaveWorksheetsAsCsv()

    Dim WS As Worksheet

    Dim newCSV As Workbook

    Dim SaveToDirectory As String

    ' Store current details for the workbook

    SaveToDirectory = "W:\public\DELMIAPartnerPortfolioReportGEO"

    Application.DisplayAlerts = False

    Application.DisplayAlerts = False

          For Each WS In ThisWorkbook.Worksheets

                Set newCSV = Application.Workbooks.Add

                      WS.UsedRange.Copy newCSV.Sheets(1).Range("A1")

                      newCSV.Rows("1:1").Delete Shift:=xlUp

                      newCSV.SaveAs Filename:=SaveToDirectory & WS.Name & ".csv", _

                      FileFormat:=xlCSVMSDOS, CreateBackup:=False

                      newCSV.Close

          Next WS

          Application.DisplayAlerts = False

          Application.DisplayAlerts = True

    End Sub

    Thank you!

    0 comments
  5. Anonymous
    2020-06-29T16:20:40+00:00

    Hi 

    Just add the line shown in BOLD to the code, that should do the job.

     For Each WS In ThisWorkbook.Worksheets

                Set newCSV = Application.Workbooks.Add

                WS.UsedRange.Copy newCSV.Sheets(1).Range("A1")

    newCSV. Sheets(1). Rows("1:1").Delete Shift:=xlUp

                newCSV.SaveAs Filename:=SaveToDirectory & WS.Name & ".csv", _

                FileFormat:=xlCSVMSDOS, CreateBackup:=False

                newCSV.Close

     Next WS

    I hope this helps you

    Regards

    Jeovany

    0 comments