Convert Workbook To Values

[lneidorf]

Hi there.

I've been working on some code, that among other things, is to convert all content to values (i.e. formulas and references). This is designed to run against multiple files in a single folder, with some of the files rather complex.

This is giving me some trouble, particularly with complex files containing a lot of sheets.


Here's my first attempt, which does work, but takes a very long time to run:

Sub ConvertAllFormulaeToValues()
    Dim WSheet As Worksheet
    For Each WSheet In ActiveWorkbook.Worksheets
        WSheet.Select
        With WSheet.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
    Next WSheet
End Sub

As you can see, this code uses the clipboard, which is highly inefficient. I need a better way.

I attempted to revise this with code that will bypass the clipboard, but it errors out:

Sub ConvertAllFormulaeToValues()
    Dim WSheet As Worksheet
    For Each WSheet In ActiveWorkbook.Worksheets
        WSheet.Select
        With WSheet.UsedRange
            .Value = .Value 'This method bypasses the clipboard, making things much more efficient.
        End With
        Application.CutCopyMode = False
    Next WSheet
End Sub

This gives me a 1004 error ("Application-defined or object-defined error").

Any suggestions would be most appreciated.

Thanks!

 

[Jeffrey Mahoney]

I made some minor changes.

You have a hidden worksheet that cannot be selected. WSheet.Select

Sub ConvertAllFormulaeToValues()
    Dim WSheet As Worksheet
    For Each WSheet In ActiveWorkbook.Worksheets
        With WSheet.UsedRange
            .Value = .Value 'This method bypasses the clipboard, making things much more efficient.
        End With
    Next WSheet
    Application.CutCopyMode = False
End Sub

 

[Fluff]

Do you have any protected sheets?
Or merged cells?

Also you do not need to activate the sheet so you cab get rid of

WSheet.Select