How to save all worksheets as values only?
If you have a workbook which contains multiple formulas, now, you need to distribute this file to other users, you don’t want to show the formulas but only values be displayed. Normally, we can quickly save a worksheet as values by copying and pasting the data as values only. But, how could you save all worksheets as values only without copying and pasting one by one?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
The following VBA code can help you to quickly save all sheets as values (no formulas) and preserve all cell formatting. Please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Save all worksheets as values only:
Sub Saveasvalue() 'Updateby Extendoffice 20161025 Dim wsh As Worksheet For Each wsh In ThisWorkbook.Worksheets wsh.Cells.Copy wsh.Cells.PasteSpecial xlPasteValues Next Application.CutCopyMode = False End Sub
3. Then press F5 key to run this code, and the whole workbook has been saved as values only, all formulas have been removed at once only leave values with cell formatting.
Note: Before you apply the code, you can save a copy of the workbook first.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 16 days agoGood! Excellent! I need this but no any sheets
- To post as a guest, your comment is unpublished.· 4 months agoRemove all filters/protection in all the sheets. should work perfectly.
- To post as a guest, your comment is unpublished.· 7 months agoTengo ese código, si bien es cierto funciona pero quisiera que no considere algunas hojas de mi archivo excel. se puede ?
- To post as a guest, your comment is unpublished.· 10 months agoFuncionou!! Muito Bom!!!
- To post as a guest, your comment is unpublished.· 1 years agowhen i run that code, i get error "getting Run time Error 1004".
FYI, in my excel there are merge cells.
It will be helpful if someone give solution
- To post as a guest, your comment is unpublished.· 10 months agoIt works well. thanks.
@mirna: you need to unprotect sheet or cell. VB doesn't like protect mode :)
- To post as a guest, your comment is unpublished.· 1 years agoHello, mirna,
The above code works well in my workbook, which excel version do you use?
- To post as a guest, your comment is unpublished.· 1 years agoSub Saveasvalue()
'Updateby Extendoffice 20161025
Dim wsh As Worksheet
For Each wsh In ThisWorkbook.Worksheets
wsh.Cells.PasteSpecial xlPasteValues - getting Run time Error 1004 - CAN ANYONE HELP ON THIS.
Application.CutCopyMode = False
- To post as a guest, your comment is unpublished.· 1 years agoThanks a lot Bro..this is amazing..helped me a lot.
- To post as a guest, your comment is unpublished.· 2 years agoVery Good! It has solved many of my problems. Thanks a lot. Regards.
- To post as a guest, your comment is unpublished.· 2 years agoThanks! The VBA code worked wonderfully.
One thing I noticed though, when I tried to use it on a workbook with Filter on some cells, it won't work.
Any workaround for this code to work without disabling the Filter?
- To post as a guest, your comment is unpublished.· 2 years agoI have the same problem when using VBA code with filter. Have you had solution for it?
- To post as a guest, your comment is unpublished.· 1 years agoHey were you able to get this fixed? I have the same issue