Maco to copy and paste data on all sheets as values

[howard]

I have the following code below to copy and paste data as vaklues onm all sheetd

 Sub convertAllSheetstoValues()

    For Each ws In ThisWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next
    
End Sub

However, when running the code, I get a run time error "Application-defined or object defined error"

The code below is highlighted

 ws.UsedRange.Value = ws.UsedRange.Value

It would be appreciated if someone could kindly amend my code

 

[mumps]

I tested your macro and it worked properly.

 

[Joe4]

Do you have any protected cells on any of your sheets?

 

[howard]

Joe, I do have protected cells

 

[howard]

I tested your macro and it worked properly.

Mumps, I am not sure what may causing this. I run a macro to unprotect all cells and then run this macro and got a run time error

 

[mumps]

Please use code tags and post the code you are using.

 

[Joe4]

Do you have any errors (or formulas that return errors) in any of your cells?
That could be causing issues also.

This should at least let you know which sheet is causing issues:

Sub convertAllSheetstoValues()

    Dim ws As Worksheet
    
    On Error GoTo err_check
    For Each ws In ThisWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    On Error GoTo 0
    
    Exit Sub
    
err_check:
    MsgBox "Error with sheet " & ws.Name
    
End Sub

 

[howard]

Thanks Joe. There were hidden columns that was cauing the issue on some of the sheets

 

[Joe4]

You are welcome.
Glad we were able to help!