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

New Member
Joined
Sep 6, 2022
Messages
4
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
  6. 2010
  7. 2007
  8. 2003 or older
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

VBA Code:
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
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top