search worksheets in different workbooks

[RichardO]

Hi All:

I have a couple of workbooks open (with a code someone helped with an
it works fine).

In my "working" worksheet, which is not included in the workbook
mentioned above, I would like to search for cell C3= Account Name e.g
Hortons and C4= Account Number e.g. 0010456 in the worksheets of th
workbook open. The worksheet name is called Raw Data. e.g. in Januar
2004 workbook, I have a worksheet called Raw Data, in February 200
workbook, I have another worksheet called Raw Data and the data i
presented in the same manner.

My question is: how do I write a maro to search cells c3 & C4 of m
working worksheet in the Raw Data worksheets mentioned earlier.

I read somewhere that autofilters are very good with vba. So I think a
easier way of searching for the values would be to autofilter the Ra
Data worksheets one at a time, and search for the value of c3 of m
working worksheet in column A of the first Raw Data worksheet and the
perform another autofilter to search in column G of the Raw Dat
worksheet for the value of c4 of my working worksheet. If excel find
rows that match, it should copy the information found in column A, G,
& K of the raw data worksheets into my working sheet starting at A20
C20 E20 and F20, so, if it finds more than 1 row of matching data, i
should find the last row after A20, and insert rows to accomodate th
data e.g. A21, C21, E21 and F21 e.t.c. If it doesn't find anything i
the Raw Data worksheet for the January 2004 workbook for example, i
should move on to the Raw Data worksheet of the February 2004 workbook
until it goes through all the Raw Data worksheets of the workbook
open, if it doesn't find anything, it should leave my workbook as is.


I would like the macro to search each worksheet one at a time because
am not sure it could search the Raw Data worksheets simultaneously.

I know if sounds really complicated, but it may be possible t
execute.

Please please hel

 

[Frank Kabel]

Hi
try the following macro (note: not fully tested):

Sub copy_wbks()
Dim Row_Index As Long
Dim LastRow As Long
Dim Target_Row As Long
Dim source_wbk As Workbook
Dim target_wbk As Workbook
Dim source_wks As Worksheet
Dim target_wks As Worksheet
Dim A_Name
Dim A_Number


Set target_wbk = ActiveWorkbook
Set target_wks = target_wbk.ActiveSheet
A_Name = target_wks.Range("C3").Value
A_Number = target_wks.Range("C4").Value
Target_Row = 20
Application.ScreenUpdating = False

For Each target_wbk In Workbooks
If LCase(target_wbk.Name) <> LCase(source_wbk.Name) Then
On Error Resume Next
Set target_wks = target_wbk.Worksheets("Raw Data")
On Error GoTo 0
If Not target_wks Is Nothing Then
LastRow = target_wks.Cells(Rows.Count, "A").End(xlUp).Row
For Row_Index = 2 To LastRow
With target_wks.Cells(Row_Index, 1)
If .Value = A_Name And .Offset(0, 6).Value = A_Number Then
target_wks.Cells(Target_Row, "A").Value = .Value
target_wks.Cells(Target_Row, "C").Value = .Offset(0,
6).Value
target_wks.Cells(Target_Row, "E").Value = .Offset(0,
7).Value
target_wks.Cells(Target_Row, "F").Value = .Offset(0,
10).Value
Target_Row = Target_Row + 1
End If
End With
Next Row_Index
End If
End If
Next target_wbk

Application.ScreenUpdating = True
End Sub