Open a workbook with all formulae as values?

[jrabbit]

This seems like a really simple thing, there must be an obvious solution.
I have a multi worksheet workbook (54 worksheets, it is awful!)
I want to get rid of some worksheets but they all have links to each other as formulae.

Can I copy and paste everything as values in all worksheets at once?

It would be really useful. I have more than one version of the above workbook

 

[JoeMo]

If you do this, you will lose all formulas - those that link to sheets you want to remove and those that are linked to sheets you want to keep. Why do you think this would be useful? Or, do you just want to create a values-only copy of your workbook?

 

[jrabbit]

If you do this, you will lose all formulas - those that link to sheets you want to remove and those that are linked to sheets you want to keep. Why do you think this would be useful? Or, do you just want to create a values-only copy of your workbook?

Yes I realise I will loose all the formulae, that's what I'm hoping for. I need to work on the data values, for a subset of the worksheets. If I delete worksheets in other sheets it will create #REF warnings. Also I can easily have a save-as of the workbook with formulas in to refer to.

A values-only copy of the workbook is exactly what I'm asking for, yes. Without me having to manually go into each of the 54 worksheets, copy all and paste as values (which is what I'd normally do)

-thanks.

 

[John Davis]

This seems like a really simple thing, there must be an obvious solution.
I have a multi worksheet workbook (54 worksheets, it is awful!)
I want to get rid of some worksheets but they all have links to each other as formulae.

Can I copy and paste everything as values in all worksheets at once?

It would be really useful. I have more than one version of the above workbook

There's more likely a better way but does this help?

Private Sub Workbook_Open()
Dim i As Long

For i = 1 To Worksheets.Count
Sheets(i).Cells.Value = Sheets(i).Cells.Value
Next i
End Sub

 

[jrabbit]

Thanks I did manage very effectively this using a macro I found on another forum, although it probably is very similar to the solution posted here.

I don't have the link but if anyone is very interested i can dig it out from my history or copy the macro out from my worksheet.

x
Thanks.