making all Sheets Values only
 

Morning All,

Is this the quickest, and only way, of making all sheets in an
activeworkbook values only:

For Each mySheet In ActiveWorkbook.Worksheets
With mySheet
.Range("A1:BZ2000").Value = .Range
("A1:BZ2000").Value
End With
Next mySheet

Will it be quicker and take up less memory if I SET the activebook
into an object variable?

Any help appreciated
Jason.

making all Sheets Values only
 

Sub valuizer()
Dim ws As Worksheet
For Each ws In Worksheets
With ws.UsedRange
.Value = .Value
End With
Next


End Sub

you can set calculation to manual and screen updatign ioff as well



"WhytheQ" wrote:

Morning All,

Is this the quickest, and only way, of making all sheets in an
activeworkbook values only:

For Each mySheet In ActiveWorkbook.Worksheets
With mySheet
.Range("A1:BZ2000").Value = .Range
("A1:BZ2000").Value
End With
Next mySheet

Will it be quicker and take up less memory if I SET the activebook
into an object variable?

Any help appreciated
Jason.

making all Sheets Values only
 

On 17 Sep, 13:25, Patrick Molloy
wrote:
Sub valuizer()
Dim ws As Worksheet
For Each ws In Worksheets
* * With ws.UsedRange
* * * * .Value = .Value
* * End With
Next

End Sub

you can set calculation to manual and screen updatign ioff as well



"WhytheQ" wrote:
Morning All,

Is this the quickest, and only way, of making all sheets in an
activeworkbook values only:

For Each mySheet In ActiveWorkbook.Worksheets
* * * * * * * With mySheet
* * * * * * * * * * * * *.Range("A1:BZ2000").Value = .Range
("A1:BZ2000").Value
* * * * * * * *End With
Next mySheet

Will it be quicker and take up less memory if I SET the activebook
into an object variable?

Any help appreciated
Jason.- Hide quoted text -

- Show quoted text -

Thanks Mr Malloy - I'll give it a go

Regards
Jason