Run-time error '1004': Application Defined or Object Defined Error

Question

This is the code I am trying to run:

Sub test()

    Dim rng As Range
    Dim iCounter As Long

    For Each rng In Worksheets(1).Range("D2", Range("D" & Rows.Count).End(xlUp))
        If rng.Value <> "" Then
            Worksheets(2).Range("D1").Offset(iCounter, 0) = rng.Value
            iCounter = iCounter + 1
        End If
    Next rng

End Sub

For some reason, if my active sheet is sheet 2, I will get the error message: Run-time error '1004': Application Defined or Object Defined Error, with the line For Each rng In Worksheets(1).Range("D2", Range("D" & Rows.Count).End(xlUp)) being highlighted. If I add Worksheets(1).Select before the For-Each loop, I do not get that error, and the code works perfectly. I don't understand why I have to explicitly select the worksheet in order to loop through the range of cells, given that I have already specified which worksheet the range of cells to be looped through is at. Also, I searched up the error and noticed that some experience a similar problem if their code is in not in the module section, but my code is indeed in the module section. Does anyone know why this error occurs? Thanks and appreciate any advice!

Michal Rosa Michal Rosa · Accepted Answer · 2020-06-19T04:22:24

You have not correctly qualified the first range, try:

Worksheets(1).Range("D2", Worksheets(1).Range("D" & Rows.Count).End(xlUp))

Run-time error '1004': Application Defined or Object Defined Error

1 Answers