compare data from multipl sheets in one workbook with multiple sheets in another wokbook

Gidday All

I have code that will look up a value in a column range in one sheet (target) against one column range over multiple sheets in another workbook (source) and paste the result into the same row of the target.
I have one sheet that i need to compare two column ranges in the target against the source, like an OR statement. when i run the original code twice it removes all the matches from the previous search.

Is there also another more efficient way of setting up the code so that I don't have to run this code multiple times for multiple sheets in the target workbook? Unfortunately the data to compare in the target workbook is not always in the same column range, nor where the result is pasted.

original code

Sub sm9()
Application.ScreenUpdating = False
 'Open Source Workbook from Objective
Workbooks.Open FileName:=("source.xls")
'activate target workbook/sheet
Windows("target.xls").Activate
Sheets("do").Visible = True
Sheets("do").Activate
For Each c In Range("ag6:ag" & Range("k65500").End(xlUp).Row)
Application.StatusBar = "processing row " & c.Row

req = Range("k" & c.Row).value
 
        Windows("source.xls").Activate 'open ticket register to search
        For Each ws In Worksheets
If ws.Name = ("2015") Or ws.Name = ("2014") Or ws.Name = ("2013") Or ws.Name = ("2012") Or ws.Name = ("2011") Then
ws.Activate
Range("g:g").Select
On Error Resume Next
Selection.Find(What:=req, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select

If ActiveCell.Row <> 1 Then
tkt9 = Cells(ActiveCell.Row, ActiveCell.Column + 7).value
stat = Cells(ActiveCell.Row, ActiveCell.Column + 19).value
dte = Cells(ActiveCell.Row, ActiveCell.Column - 6).value
GoTo 10
Else: End If
Else: End If

Next ws

10
'activate target workbook/sheet
Windows("target.xls").Activate
c.value = tkt9
c.Offset(0, 1).value = stat
c.Offset(0, 2).value = dte
tkt9 = ""
stat = ""
dte = ""
req = ""

Next c
Application.ScreenUpdating = True
Application.StatusBar = False
 'Close Source Workbook
       Windows("source.xls").Activate
        Application.CutCopyMode = False
        Range("A2").Select
        Application.DisplayAlerts = False
       ' Workbooks("source.xls").Close
        Application.DisplayAlerts = True
        
MsgBox "complete", vbOKOnly
End Sub

Hi vbapadawan ,

It is difficult to follow your code without the workbooks, can you post them? (Sanitized if confidential data, and small sample is better).

I think there is a better way of doing the job, by building an array within the macro, then posting once.

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.