Run time error

[rjplante]

I am trying to build a macro that will copy the cells from C2 to the bottom of the column across several sheets and paste them into one column on the first worksheet. When I run the macro below, it crashes on the "set rng..." line with the error, "Run-time error '1004': Method 'Range' of object'_Worksheet' failed". Can someone help me figure out what I am missing with this one?

Thanks for the help,

Robert

Sub Extract()


Dim ws As Worksheet
Dim rng As Range


For Each ws In ActiveWorkbook.Worksheets
'If ws.Name <> "Combined" Then


    If ws.Name <> "Combined" Then
        Set rng = ws.Range("C2" & ws.Rows.Count).End(xlUp)
        'Storing first copied data in A2
        If IsEmpty(Sheets("Combined").[A2]) Then
            rng.Copy Sheets("Combined").Range("A2" & Rows.Count).End(xlUp)
        'Storing next copied data below previously filled cell
        Else
            rng.Copy Sheets("Combined").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End If
Next ws


End Sub

 

[Fluff]

Try

        Set rng = ws.Range("C2", ws.Range("C" & ws.Rows.Count).End(xlUp))

 

[rjplante]

Awesome! Thanks for the fix. The next line failed with the same error, but I used your fix on the first line and incorporated it into the next line of code and it worked. Thanks again.

 

[Fluff]

Glad to help & thanks for the feedback

If by next line, you mean this line

rng.Copy Sheets("Combined").Range("A2" & Rows.Count).End(xlUp)

I think it should be

rng.Copy Sheets("Combined").Range("A2")