Vba- Macro to save sheets as new workbook, but Values, without formulas?

[mnty89]

Hi All,

I think this will be very simple, but I'm not sure where exactly to tweak which line. I have the Vba below which works wonders to split the current workbook into new files for each sheet. My one change I need is to have it save each file with the values in the cells and not the formulas used to populate the values.

Currently it saves a duplicate of the sheet with formulas so when it is sent the numbers don't populate correctly.

Can someone please help edit this Vba code to save the sheets as a value version of itself but keep everything else the same(formatting, color etc)?

I need to have the sheet remain with formulas as well since it will feed in updates based on the other master sheets as well.. So I cannot simply copy and paste value of this sheet before the split.

Sub Splitbook()
Dim xWs As Worksheet
Dim xPath As String
xPath = Application.ActiveWorkbook.Path & "\Carrier Files"
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


Sheets("Landing Page").Select


MsgBox ("Done.")


End Sub

 

[svendiamond]

I need to have the sheet remain with formulas as well

You mean the original sheet? I don't understand. You're saying you want to just paste the values but here you're saying you want to keep the formulas... ?

 

[JoeMo]

Insert the red line where shown below:

xWs.Copy
activesheet.usedrange.value = activesheet.usedrange.value
Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xlsx"

 

[mnty89]

Sorry that is really unclear..

I meant I need to have the original sheet remain it is the way it starts with formulas, formatting etc, but the version that is created by the macro that is saved as an external file with just the one sheet will be just the values, not have formulas.

That macro blindly takes each sheet and makes it its own new workbook. I just wanted to add something to make that save each sheet as the value version of itself as opposed to an exact copy..

 

[JoeMo]

Sorry that is really unclear..

I meant I need to have the original sheet remain it is the way it starts with formulas, formatting etc, but the version that is created by the macro that is saved as an external file with just the one sheet will be just the values, not have formulas.

That macro blindly takes each sheet and makes it its own new workbook. I just wanted to add something to make that save each sheet as the value version of itself as opposed to an exact copy..

See post #3

 

[mnty89]

Thank you! I knew it would be a simple one liner to be added!

 

[JoeMo]

Thank you! I knew it would be a simple one liner to be added!

You are welcome - thanks for the reply.

 

[jkhoury1]

Hi Joe,
I am using this code nd works great! thnkyou!!
my question is, what if I only want a workbook as a whole to copy over, not each workseet?

 

[JoeMo]

Hi Joe,
I am using this code nd works great! thnkyou!!
my question is, what if I only want a workbook as a whole to copy over, not each workseet?

If you still want to save it with just values (no formulas) on each sheet, then

for each xWs in activeworkbook.Worksheets
xWs.usedrange.value = xWs.usedrange.value
Next xWs
Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xlsx"

Replace the worksheet name in red with a specific worksheet name you want to use.