Private Function MakeSheetValuesOnlyR1(mySheetName As String) as Byte
On Error GoTo LowValue
With ActiveWorkbook.Sheets(mySheetName).UsedRange
On Error Resume Next
.Value = .Value
If Err.Number < 0 Then
On Error GoTo LowValue
.Copy
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Else
On Error GoTo LowValue
End If
End With
ActiveWorkbook.Sheets(mySheetName).Select
ActiveWorkbook.Sheets(mySheetName).Range("A1").Sel ect
Exit Function
LowValue:
Beep
End Function
'---

Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)




"WhytheQ"
wrote in message
...
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
'=========================================