Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Apr 7 '13 at 4:48

This question came from our site for professional and enthusiast programmers.

Would saving as a CSV file work? – ikh Apr 6 '13 at 19:27
It has to be an excel sheet. – murali dhar Apr 6 '13 at 19:37
Copy the sheet (rather than the data) [prior to Copy/Paste Special/Values] does not suit? – pnuts Apr 6 '13 at 21:33
if i copy sheet. It also copies formulas. – murali dhar Apr 7 '13 at 1:25

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.PasteSpecial xlPasteValues

    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.

share|improve this answer
ya i know how to get rid of macros. I mean about .xlsx. is the above code is for macro? – murali dhar Apr 7 '13 at 1:24
Yes. In point A. I have mentioned both VBA and Non VBA Methods :) – Siddharth Rout Apr 7 '13 at 7:36

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.

share|improve this answer
It breaks links to other files, but won't replace all formulas to values. – Máté Juhász Jun 29 at 12:45

You must log in to answer this question.