0

I'm new to VBA, and I got stuck with a simple problem. I want to select all cells with data in the worksheet, paste them by value, then replace "#NUM!" with ""(nothing) and repeat all the above steps in all worksheets. The issue is that it wouldn't loop through all worksheets. Please help. 

Sub Test()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

'Select all, copy and paste-by-value
ws.Range("A1").CurrentRegion.Value = ws.Range("A1").CurrentRegion.Value

'Find and Replace
Cells.Replace What:="#NUM!", Replacement:=""

Next ws

End Sub
Community
  • 1
  • 1
T-T
  • 693
  • 1
  • 10
  • 24
  • Possible duplicate of [VBA Excel macro: use Range to act on a different worksheet](http://stackoverflow.com/questions/2656443/vba-excel-macro-use-range-to-act-on-a-different-worksheet) – GSerg May 04 '16 at 22:21
  • Sorry, but how's my post duplicate to that one? I checked out that post but I don't see a solution to my question. – T-T May 04 '16 at 22:27
  • 5
    Maybe he meant the missing `ws.` at the start of the line `Cells.Replace What:="#NUM!", Replacement:=""`? – Ralph May 04 '16 at 22:29
  • 4
    As @Ralph says, you're missing the `ws.` before `Cells`. Voting to close the as being the result of a simple typographic error. – Ken White May 04 '16 at 22:39
  • Ralph and Ken are right. Thanks for the help! – T-T May 05 '16 at 15:05

0 Answers0