Macro to Paste Special Values, Remove Sheets and Save

Hi Guys,

I have a spreadsheet that has several worksheets, some with raw data and others with output and a Control Sheet. On the control sheet, I want to create a button that will remove all the formulas on every other sheet (paste special values) and save a copy of the file without any of the forumlas, or macros, and without the raw data sheets.

Is this possible?

Cheers,

Dex!

Hi
try the following macro codes. change file name and sheet name to suit your case. cell A1 should have the new file name.

Sub Dextadark()
Dim a As Integer
Dim b As String
b = Workbooks("cmf.xls").Worksheets("sheet1").Cells(1, 1) & ".xls"
Workbooks.Add
ActiveWorkbook.SaveAs b
For a = 1 To Workbooks("cmf.xls").Sheets.Count
Workbooks("cmf.xls").Worksheets(a).UsedRange.Copy
Workbooks(b).Worksheets(a).Range("A1").PasteSpecial xlValues
Next a
End Sub

run the macro. it copies all sheets and pastes special into a new workbook. Now how do you Know which is raw data and which is derivative data
ravi