How to save the Excel sheet without formula and macros?

We have an Excel sheet with some formula and macros. We want to save the data as an another Excel sheet without formula and macros before we run macros on the current sheet.

It should just save the values. One way is copy the entire data and paste them as values in another Excel sheet and save it. But is there any other simple way to do this?

2 Answers

It's a two step process

A. Loop through all sheets and copy the cells and then do a pastespecial values (No need to copy it to another sheet)

Sub Sample() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Cells.Copy ws.Cells.PasteSpecial xlPasteValues Next Application.CutCopyMode = False
End Sub

IF you don't want to go the VBA way then you will have to manually copy and do PasteSpecial. Once the above is done

B. You can get rid of the macros by saving the file as .xlsx. You will have to close and reopen the file. This way your original file will remain untouched.

Go to Data in the ribbon at the top of excel, click on edit links (in connections part of that toolbar near the middle, and then break link for every link seen.