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

[nikdano]

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!

 

[smozgur]

Welcome to the MrExcel Message Board!

As you already found, this is a similar question in this thread, with a similar solution, except you want to save the file in the current Excel file path (which I assume is the same path as the current workbook). You also mentioned "specific sheet", so we can instead use the sheet name instead of copying the active sheet.

Copy and paste the following macro into a new module in your workbook, and run it.

Sub saveSheetWithoutFormulasInTheSamePath()
    ' Copy the sheet by using its name
    ' Note: Change "MySheet" in the code with the worksheet name
    ' This automatically creates a new workbook
    ' with the sheet copied in it
    ThisWorkbook.Worksheets("MySheet").Copy
    
    ' Active sheet is now the copied sheet in the new workbook
    ' Following will change formulas to values
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
    ActiveWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & ActiveSheet.Name
End Sub

 

[nikdano]

Welcome to the MrExcel Message Board!

As you already found, this is a similar question in this thread, with a similar solution, except you want to save the file in the current Excel file path (which I assume is the same path as the current workbook). You also mentioned "specific sheet", so we can instead use the sheet name instead of copying the active sheet.

Copy and paste the following macro into a new module in your workbook, and run it.

Sub saveSheetWithoutFormulasInTheSamePath()
    ' Copy the sheet by using its name
    ' Note: Change "MySheet" in the code with the worksheet name
    ' This automatically creates a new workbook
    ' with the sheet copied in it
    ThisWorkbook.Worksheets("MySheet").Copy
   
    ' Active sheet is now the copied sheet in the new workbook
    ' Following will change formulas to values
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
   
    ActiveWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & ActiveSheet.Name
End Sub

Yes It's work perfect! Thank you so much sir, but I want to save file name from cell A1.value