How to copy particular columns and paste in another sheet on vba

[carac]

Hi,

I have been trying to copy certain columns of two spreadsheets based on whether or not they match a particular cell, and then paste them in the another worksheet. This is the code I have so far:

Sub search()
Dim i As Integer
Dim j As Integer
Dim k As Long
Dim counter As Integer
Dim Name As String
counter = 2
For i = 2 To 5
Name = Sheets("Sold").Cells(i, "B").Value
For j = 1 To 2278
If Name = Sheets("Case Data").Cells(j, "D") Then
Sheets("Sheet1").Cells(counter, "A").Value = Name
With Sheets("Case Data")
Worksheets("Sheet2").Range(.Cells(j, "C"), .Cells(j, "AE")).Copy _
Destination:=Worksheets("Sheet4").Range(Sheets("Sheet4").Cells(counter, "B"))
counter = counter + 1
End With
End If
Next j

For k = CLng(2) To 40525
If Name = Sheets("Claims").Cells(k, "G") Then
Sheets("Sheet1").Cells(counter, "A").Value = Name
Sheets("Claims").Range(Cells(k, "S")).Copy _
Sheets("Sheet1").Range(Cells(counter, "AE"))
Sheets("Claims").Range(Cells(k, "X")).Copy _
Sheets("Sheet1").Range(Cells(counter, "AF"))
counter = counter + 1
End If
Next k
Next i


End Sub



I keep getting a 1004 error under the With statement. Any thoughts or suggestions?
Thank you!!

 

[Takae]

With Sheets("Case Data")
Worksheets("Sheet2").Range(.cells(j, "C"), .cells(j, "AE")).copy _

These dots mean the range belong "With Sheets("Case Data")".
So this line says Worksheets("Sheet2").Range(Sheets("Case Data").cells(j, "C"), Sheets("Case Data").cells(j, "AE")).copy _
This is non-existent range.