Best way to make all sheets in a workbook Values only

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").Select
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
news:

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
'==

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

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