.Value = .Value (error) (Reading data from one workbook and populating in another)

[agohir]

Hello Excel Community,

I am trying to hardcode each worksheet in a workbook. I have the following code that gives an "Overflow" error when I run it on .Value = .Value

Any help with the code please:

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Value = .Value

End With
Next ws

The error I receive is on .Value = .Value
Is there a way I can hardcode worksheets using a different code. I tried copy paste special but the result was "true" filled in all cells.

Please help.

Thanks for the help.

 

[My Aswer Is This]

Try this:

Sub Do_All()
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To Sheets.Count
        With Sheets(i).UsedRange
            .Value = .Value
        End With
    Next
Application.ScreenUpdating = True
End Sub

 

[StephenCrump]

I have the following code that gives an "Overflow" error when I run it on .Value = .Value

Definitely an overflow error, and not an out of memory error?

If so, I suggest you check for cells that look like this: ############## which are dates/times that are negative or too big.

It looks like over-large dates are the problem, e.g. if A1 contains 9999999 formatted as a date, then this line of code will give you an overflow error:

Range("A1").Value = Range("A1").Value