On Jul 8, 5:54*am, WhytheQ wrote:
this looks nice Garry - happy to avoid the clipboard - I'll give it a
go

J

On Jul 1, 5:21*pm, GS wrote:



WhytheQ formulated the question :

Hello All,

I've come across a couple of methods of making all sheets in a
workbook values only. The routines I have have a tentency towards
unexpected errors and they take a while to run e.g below. Has anyone
got an alternative method I could try.

Any help appreciated
Jason.

'========================
Private Function MakeSheetValuesOnly(mySheetName As String)

With ActiveWorkbook.Sheets(mySheetName)
* * * * With .Cells
* * * * * * .Copy
* * * * * * .PasteSpecial xlPasteValues
* * * * End With
* * * * .Select
* * * * .Range("A1").Select
End With
ActiveWindow.SmallScroll Down:=-200
Application.CutCopyMode = False

End Function * * * * * * * 'MakeSheetValuesOnly
'=========================================

Try...

Sub MakeSheetValuesOnly2()
* Dim wks As Worksheet
* For Each wks In ActiveWorkbook.Worksheets
* * With wks.UsedRange
* * * .Value = .Value
* * End With 'wks.UsedRange
* Next 'wks
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Try:
Sub Formula_Zapper()
Worksheets.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Select
Application.CutCopyMode = False
End Sub