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

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" <(E-Mail Removed)>
wrote in message
news:e9087cf9-1cf7-48f3-b99d-(E-Mail Removed)...
> 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 <g...@somewhere.net> 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 <whyt...@gmail.com> wrote:
> this looks nice Garry - happy to avoid the clipboard - I'll give it a
> go
>
> J
>
> On Jul 1, 5:21*pm, GS <g...@somewhere.net> 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