PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|Stop macro from going to a different sheet in Excel||18||50|
|Records being deleted when Condition Not Met||30||28|
|Excel Import/Export Named Ranges and Values||4||31|
|VBA to Import multiple excel files into a worksheet||5||14|
Join the community of 500,000 technology professionals and ask your questions.