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.

 

[smozgur]

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

 

[emadhamrawi]

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

Thank you so much for your quick response. That was perfect. I just need one more thing. I used the path for the saveas, but I want the file name to be the value of cell H1. How can I do that?

 

[smozgur]

I used the path for the saveas, but I want the file name to be the value of cell H1. How can I do that?

Does it mean that you don't want to open the Save As dialog, but automatically save the file by using the file name (with full path) written in cell H1?

 

[smozgur]

Both option could be useful for the future readers. So, I'll answer for both.

If you still want to see the Save As dialog but with a default file name which is provided in cell H1, then change the last code line with the following:

Application.GetSaveAsFilename Range("H1")

If the path and file name is predefined, and stored in cell H1, then use the following instead:

ActiveWorkbook.SaveAs Range("H1")

 

[emadhamrawi]

Does it mean that you don't want to open the Save As dialog, but automatically save the file by using the file name (with full path) written in cell H1?

Yes, that's right

 

[emadhamrawi]

Both option could be useful for the future readers. So, I'll answer for both.

If you still want to see the Save As dialog but with a default file name which is provided in cell H1, then change the last code line with the following:

Application.GetSaveAsFilename Range("H1")

If the path and file name is predefined, and stored in cell H1, then use the following instead:

ActiveWorkbook.SaveAs Range("H1")

Path and filename are predefined but only the file name is stored in H1.

 

[smozgur]

Path and filename are predefined but only the file name is stored in H1.

Where is the path then if it is not in H1?

Did you notice my previous message contains ActiveWorkbook.SaveAs - which I also mentioned in the original code?

 

[nikdano]

Where is the path then if it is not in H1?

Did you notice my previous message contains ActiveWorkbook.SaveAs - which I also mentioned in the original code?

Hello Sir! If I want to save in current excel file path

 

[smozgur]

Hello Sir! If I want to save in current excel file path

Answered your question in its own thread:

Excel VBA to export and save sheet as a new workbook without formulas in current excel file and save file name from cell value.

Hello all guy! I need a VBA code that helps me export an s specific sheet and save it as an excel workbook without formulas and save in current excel file location and save file name get from cell value. Example: Export Sheet1 and save file name from cell A1. Thank!

www.mrexcel.com