Excel VBA to export and save sheet as a new workbook without formulas

[emadhamrawi]

I need a VBA code that helps me export an excel sheet and save it as an excel workbook without formulas.

 

[Raastaa]

Welcome to the MrExcel Message Board!

Try the following macro in a standard module. It is only three lines actually, but looks more with comments. Follow the comments to understand how it works.

Sub saveSheetWithoutFormulas()
    ' Copy the active sheet
    ' This automatically creates a new workbook
    ' with the sheet copied in it
    ActiveSheet.Copy
   
    ' Active sheet is now the copied sheet in the new workbook
    ' Following will change formulas to values
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
   
    ' Open a file save dialog to save the new workbook
    ' If you know where to save with a predefined file name
    ' then you can use ActiveWorkbook.SaveAs "C:\FolderPath\FileName.xlsx"
    Application.GetSaveAsFilename

End Sub

If you want to make it work with a certain worksheet which is not supposed to be the active sheet when you run the code, then you need to change the first line, ActiveSheet.Copy, with the following by using the actual sheet name instead of "Sheet1".

Worksheets("Sheet1").Copy

Welcome to the MrExcel Message Board!

Try the following macro in a standard module. It is only three lines actually, but looks more with comments. Follow the comments to understand how it works.

Sub saveSheetWithoutFormulas()
    ' Copy the active sheet
    ' This automatically creates a new workbook
    ' with the sheet copied in it
    ActiveSheet.Copy
   
    ' Active sheet is now the copied sheet in the new workbook
    ' Following will change formulas to values
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
   
    ' Open a file save dialog to save the new workbook
    ' If you know where to save with a predefined file name
    ' then you can use ActiveWorkbook.SaveAs "C:\FolderPath\FileName.xlsx"
    Application.GetSaveAsFilename

End Sub

If you want to make it work with a certain worksheet which is not supposed to be the active sheet when you run the code, then you need to change the first line, ActiveSheet.Copy, with the following by using the actual sheet name instead of "Sheet1".

Worksheets("Sheet1").Copy

How can add date or time in name of new excel workbook that exported with this code?

 

[Peter_SSs]

How can add date or time in name of new excel workbook that exported with this code?

Duplicate to: Excel VBA to export and save sheet as a new workbook that new workbook's name contains current date or time without formulas,.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this old thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.