Error with code that paste on next available row

[J.W. Aldridge]

The second bottom half of this code is supposed to paste the range to
the sheet "data" (A:F) on the next available row.

It worked once somehow but, I can't get past the error on this line
now.

Range(cell, Cells(cell.Row, "g")).Copy sh.Cells(NewRow, 1)


Sub ABC2()
Dim cell As Range, c As Range
Dim i As Range, rw As Long
Dim sh As Worksheet
With Worksheets("List")
Set c = .Range(.Cells(6, "B"), .Cells(6, "B").End(xlDown))
Set i = .Range(.Cells(6, "I"), .Cells(6, "I").End(xlDown))
End With


Set sh = Worksheets("Data")
LastRow = sh.Cells(Rows.Count, "a").End(xlUp).Row
NewRow = LastRow + 1
For Each cell In c
If Application.CountIf(i, cell) > 0 Then
Range(cell, Cells(cell.Row, "g")).Copy sh.Cells(NewRow, 1)
NewRow = NewRow + 1
End If
Next
End Sub

 

[meh2030]

The second bottom half of this code is supposed to paste the range to
the sheet "data" (A:F) on the next available row.

It worked once somehow but, I can't get past the error on this line
now.

Range(cell, Cells(cell.Row, "g")).Copy sh.Cells(NewRow, 1)

Sub ABC2()
Dim cell As Range, c As Range
Dim i As Range, rw As Long
Dim sh As Worksheet
With Worksheets("List")
Set c = .Range(.Cells(6, "B"), .Cells(6, "B").End(xlDown))
Set i = .Range(.Cells(6, "I"), .Cells(6, "I").End(xlDown))
End With

Set sh = Worksheets("Data")
LastRow = sh.Cells(Rows.Count, "a").End(xlUp).Row
NewRow = LastRow + 1
For Each cell In c
If Application.CountIf(i, cell) > 0 Then
    Range(cell, Cells(cell.Row, "g")).Copy sh.Cells(NewRow, 1)
    NewRow = NewRow + 1
End If
Next
End Sub

There is only one way I can think of that would throw an error with
the code you listed. This would be that the worksheet that you are
copying to has no more room to accommodate the copy (i.e. you exceed
the row size limit for the worksheet). Otherwise, please provide the
error that is occurring and the context in which the error is
occurring. You may want to step through your code to see how it
operates and to see "where" your error is occurring. You can do this
by pressing the F8 key multiple times.

Matt Herbert

 

[Joel]

I think your problem is occuring because you aren't specifying a worksheet.
try this

Sub ABC2()
Dim cell As Range, c As Range
Dim i As Range, rw As Long
Dim sh As Worksheet

Set sh = Worksheets("Data")
LastRow = sh.Cells(Rows.Count, "a").End(xlUp).Row
NewRow = LastRow + 1

With Worksheets("List")
Set c = .Range(.Cells(6, "B"), .Cells(6, "B").End(xlDown))
Set i = .Range(.Cells(6, "I"), .Cells(6, "I").End(xlDown))

For Each cell In c
If Application.CountIf(i, cell) > 0 Then
.Range(cell, .Cells(cell.Row, "G")).Copy sh.Cells(NewRow, "A")
NewRow = NewRow + 1
End If
Next
End With

End Sub

 

[J.W. Aldridge]

Run-time error '1004';
Method 'Range' of object'_Global' failed

I ran through the code as suggested...

Just as I reached the next line, I got the error message above.

Range(cell, Cells(cell.Row, "g")).Copy sh.Cells(NewRow, 1)
NewRow = NewRow + 1

I am only at line 3197 so I have plenty of room left. The rest of the
worksheet below and to the right is empty.

 

[Joel]

Did you look at my last response? I moved the With statement to include the
line you have in error and then added two periods so the sheet "List" is
referenced in the statement.