Save excel active worksheet as a csv file

Hi Experts!

I have an excel file with 4 worksheets. I have a button, on which when the user clicks is able to save the current active worksheet as a csv file. It would be fancy that there is a save as dialog where the user could choose the folder, but if that will be too much I would be happy to have the file saved in the same directory as the excel file with the name : activeWorksheet_currentDate.csv


What I have so far is this macro. However this saves all worksheets available in the file and I have a problem that I need to remove all the empty cell lines

Moreover I need to remove the first 2 lines in the exported csv file.

I am not a VBA expert so I put together this code from several locations. The problem I am facing when deleting and restoring rows is that the previous formating of the rows is gone. I have some olor backgrounds for delimiters and header names.

Private Sub CommandButton1_Click()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long
Dim fileName As String

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
 
 fileName = "export_" + Format(Now(), "ddmmyyyy")
 
    Dim values
    Dim usedRangeAddress As String
    Dim r As Range
    'Get UsedRange Address prior to deleting Range
    usedRangeAddress = ActiveSheet.UsedRange.Address
    'Store values of cells to array.
    values = ActiveSheet.UsedRange
    'Delete all cells in the sheet
    'ActiveSheet.Cells.Delete
    'Restore values to their initial locations
    'Range(usedRangeAddress) = values
' Store current details for the workbook

      'SaveToDirectory = "C:\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name, xlCSV
      Next

 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs fileName:=CurrentWorkbook, FileFormat:=CurrentFormat
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

Select allOpen in new window