View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
joel joel is offline
external usenet poster
 
Posts: 9,101
Default Error with code that paste on next available row

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" wrote:

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