Search in multiple sheets

[dado6612]

Hello, I have this code which works perfectly if active sheet has needed search value, otherwise it's an error.
How to adapt this so it searches thru more sheets instead. Thanks
The code is in plan report workbook, looking a value from weekly plan workbook and searches withing plan report workbook, then copies offset cells back to weekly plan workbook.

Sub search()Application.ScreenUpdating = False
Dim rng As Range
Dim order As String
Dim rn As Long
Dim name As String
Dim county As String
Dim part As String
Dim desc As String
Dim active As Range
Windows("Weekly Plan.xls").Activate
Set active = ActiveCell
order = ActiveCell.Text
Windows("Plan_Report.xlsm").Activate
Set rng = ActiveWorkbook.ActiveSheet.Columns("D:D").Find(what:=order, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    rn = rng.Row
    name = ActiveWorkbook.ActiveSheet.Cells(rn, 5).Value
    county = ActiveWorkbook.ActiveSheet.Cells(rn, 12).Value
    part = ActiveWorkbook.ActiveSheet.Cells(rn, 6).Value
    desc = ActiveWorkbook.ActiveSheet.Cells(rn, 11).Value
    Windows("MASTER SILO Weekly Plan 2018.xls").Activate
    active.Offset(-4, 0).Value = name
    active.Offset(-3, 0).Value = county
    active.Offset(-2, 0).Value = desc
    active.Offset(-1, 0).Value = part
    Application.ScreenUpdating = False
End Sub

 

[dado6612]

Okay, got this, after a run it does it's job but then it crashes, it goes into an error and selects line
rn=rng.row
as an error: Object variable or with block variable not set
Any ideas why and how to fix it? Thanks

Sub search()Application.ScreenUpdating = False
Dim rng As Range
Dim order As String
Dim rn As Long
Dim name As String
Dim county As String
Dim part As String
Dim desc As String
Dim active As Range
Dim ws As Worksheet
Windows("Weekly Plan.xls").Activate
Set active = ActiveCell
order = ActiveCell.Text
Windows("Plan_Report.xlsm").Activate
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Set rng = ActiveSheet.Columns("D:D").Find(What:=order, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    [B]rn = rng.Row[/B]
    name = ActiveWorkbook.ActiveSheet.Cells(rn, 5).Value
    county = ActiveWorkbook.ActiveSheet.Cells(rn, 12).Value
    part = ActiveWorkbook.ActiveSheet.Cells(rn, 6).Value
    desc = ActiveWorkbook.ActiveSheet.Cells(rn, 11).Value
    Windows("Weekly Plan 2018.xls").Activate
    active.Offset(-4, 0).Value = name
    active.Offset(-3, 0).Value = county
    active.Offset(-2, 0).Value = desc
    active.Offset(-1, 0).Value = part
    Application.ScreenUpdating = True
    Next ws
    Windows("Weekly_Plan.xls").Activate
End Sub

 

[dado6612]

Well did it with simple on error goto.. it did the trick, just still confused what the actual problem is..

 

[dado6612]

Well it seems like its not working. Anyone can help?