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_currentDat
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 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
'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
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.