Your question, your audience. Choose who sees your identity—and your question—with question security.
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
Join the community of 500,000 technology professionals and ask your questions.