Convert entire workbook to values

[Kaps_mr2]

I am trying to find the most efficient way of converting an entire workbook to values. I did find the following code from another site :-

However I get an error on the line in bold. Can anybody tell me why ? Thanks

Regards

Kaps

Public nirvana_workbook as workbook
 
Public sub convert_to_values()
nirvana_workbook.activate
 
 Worksheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Select
    Application.CutCopyMode = False
End Sub

 

[GlennUK]

Maybe it's my screen, but I can't see a bold line.

Where is nirvana_workbook set up?

Shouldn't you be looping through the sheets of the workbook ... your current logic doesn't look like it would work even if you got the syntax right?

For Each ws in nirvana_workbook.WorkSheets
     ws.UsedRange.Values = ws.UsedRange.Values 
Next

 

[Kaps_mr2]

Hi Glenn,

Thanks - I got this from another site - it seemed to try to work by selecting the entire workbook in one go. Your approach does make sense - much appreciated.

kind regards

Kaps

 

[gsbelbin]

Your code works fine for me. I wasn't aware you could do all the sheets at once but it does work. Learn something every day! I can't see why it doesn't like selecting the worksheets. I presume you've tried stepping through the code. What error is it giving you?

 

[Kaps_mr2]

Actually Glenn I get object does not support this property or method on the second line of your code:-


For Each ws in nirvana_workbook.WorkSheets ws.UsedRange.Values = ws.UsedRange.Values Next</pre>
gsbelbin I can't remember the exact error I got but it was along the same lines. Unfortunately my macro takes an age to run (lots of large data files).

Thinking I probably need to combine the two approaches:-

For Each ws in nirvana_workbook.WorkSheets
ws.cells.select
selection.copy
selection.pastespecial paste:=xlpastevalues
ws.select
application.cutcopymode = false
next

</pre>

One of my worksheets has 150k rows in it - so I am looking for a fast solution. thanks

Kaps

 

[GlennUK]

Typo,

ws.UsedRange.Values

should be:

ws.UsedRange.Value

 

[Kaps_mr2]

Hi Glenn,

I now get a fatal "Out of memmory error" - any other thoughts ? Thanks

Regards

Kaps

 

[GlennUK]

And do you know how far through the processing it gets before that error occurs? Have you tried printing out the UsedRange address at very loop, to see if it's a particular range size that causes the problem?

 

[Kaps_mr2]

Hi Glenn,

The largest worksheet "Tech Detail" has 171,000 rows on it - and this one causes the error.

I don't actually need this worksheet. I did try and do all the other worksheets as values and delete "Tech Detail" at the end but got REF# errors everywhere. Any thoughts ? Thanks

Regards

Kaps

 

[GlennUK]

Hi Kaps,

I don't actually need this worksheet. I did try and do all the other worksheets as values and delete "Tech Detail" at the end but got REF# errors everywhere. Any thoughts ? Thanks

How can #REF! errors appear in cells that have already been converted to values? That is not possible.