Error 1004 in search/copy

[ron_dallas]

Ooo Great ones,
I have the following:
1) Start at J3 and go down
2) find that info in different S/S
3) return different cell info to cells M3 to T3

Any suggestions?

Thanks in advance!!!
Ron

Sub Pull_From()

Dim rng As Range
Dim sh As Worksheet
Dim rng1 As Range
Dim cell As Range
With Workbooks("dallas.xls").Worksheets("sheet1")
Set rng1 = .Range(.Cells(3, 11), _
.Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng1
For Each sh In Workbooks("detailed report.xls").Worksheets
Set rng = Nothing
Set rng = sh.Cells.Find(What:=cell, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then Exit For
Next
If Not rng Is Nothing Then
cell.Offset(0, 13).Value = rng.Offset(0, -10).Value
cell.Offset(0, 14).Value = rng.Offset(0, 5).Value
cell.Offset(0, 15).Value = rng.Offset(0, 4).Value
cell.Offset(0, 16).Value = rng.Offset(0, 1).Value
cell.Offset(0, 17).Value = rng.Offset(0, 2).Value
cell.Offset(0, 18).Value = rng.Offset(0, -1).Value
cell.Offset(0, 19).Value = rng.Offset(0, -2).Value
cell.Offset(0, 20).Value = rng.Offset(0, -11).Value
End If
Next


End Sub

 

[Tom Ogilvy]

I would try changing

Set rng1 = .Range(.Cells(3, 11), _
.Cells(Rows.Count, 1).End(xlUp))

to

Set rng1 = .Range(.Cells(3, 11), _
.Cells(Rows.Count, 11).End(xlUp))

and then see how it performs.

 

[ron_dallas]

Thanks, That worked, don't understand why, but thats OK!!!!!!

I was wanting to remove the loop
For Each sh In Workbooks("detailed report.xls").Worksheets

Could you tell me what needs to be deleted. I want it to search only
sheet1

Thanks,
Ron

 

[Tom Ogilvy]

Sub Pull_From()

Dim rng As Range
Dim sh As Worksheet
Dim rng1 As Range
Dim cell As Range
With Workbooks("dallas.xls").Worksheets("sheet1")
Set rng1 = .Range(.Cells(3, 11), _
.Cells(Rows.Count, 11).End(xlUp))
End With
For Each cell In rng1
set sh = Workbooks("detailed report.xls").Worksheets("Sheet1")
Set rng = Nothing
Set rng = sh.Cells.Find(What:=cell, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
cell.Offset(0, 13).Value = rng.Offset(0, -10).Value
cell.Offset(0, 14).Value = rng.Offset(0, 5).Value
cell.Offset(0, 15).Value = rng.Offset(0, 4).Value
cell.Offset(0, 16).Value = rng.Offset(0, 1).Value
cell.Offset(0, 17).Value = rng.Offset(0, 2).Value
cell.Offset(0, 18).Value = rng.Offset(0, -1).Value
cell.Offset(0, 19).Value = rng.Offset(0, -2).Value
cell.Offset(0, 20).Value = rng.Offset(0, -11).Value
End If
Next


End Sub