Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com 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
add comment

1 Answer

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.

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.