Save Worksheets As Workbooks As Values

tfla

New Member
Joined
Mar 17, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello!

I am very new to Excel macros (read: i know nothing). But i've found this macro online, which saves all sheets/tabs as workbooks. It uses the sheet names to create the file names, and saves them all in the same folder as the original:

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs fileName:=xPath & "\" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

I've also found this code, which saves all Worksheets as values instead of formulas:

Sub Saveasvalue()
'Updateby Extendoffice
Dim wsh As Worksheet
For Each wsh In ThisWorkbook.Worksheets
wsh.Cells.Copy
wsh.Cells.PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
End Sub

And I kinda want to combine these two somehow, so that the output files of the first formula contains only values. However i only want the new files to be formula-free, not the original document. I still want the formulas to be in the original. Code-wise i think i would probably either need a code that opens every file except the original, and runs the PasteValues-script on all of them.. Or some code that makes a copy of all sheets, saves them as values, then deletes the copies again. But I don't really know and have no idea how to even start on either, so any help would be appreciated :)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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