search worksheets in different workbooks

[SharonMee]

Hi All:

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

In my "working" worksheet, which is not included in the workbooks 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 the workbook open. The worksheet name is called Raw Data. e.g. in January 2004 workbook, I have a worksheet called Raw Data, in February 2004 workbook, I have another worksheet called Raw Data and the data is presented in the same manner.

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

I read somewhere that autofilters are very good with vba. So I think an easier way of searching for the values would be to autofilter the Raw Data worksheets one at a time, and search for the value of c3 of my working worksheet in column A of the first Raw Data worksheet and then perform another autofilter to search in column G of the Raw Data worksheet for the value of c4 of my working worksheet. If excel finds rows that match, it should copy the information found in column A, G, H & 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, it should find the last row after A20, and insert rows to accomodate the data e.g. A21, C21, E21 and F21 e.t.c. If it doesn't find anything in the Raw Data worksheet for the January 2004 workbook for example, it should move on to the Raw Data worksheet of the February 2004 workbook, until it goes through all the Raw Data worksheets of the workbooks 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 I am not sure it could search the Raw Data worksheets simultaneously.

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

Please please help.

 

[PHV]

And what have you so far ?
Have you tried the macro recorder to get a starting point ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SharonMee]

Hi, Here is what I have been able to record. I found out my macro recorder can not record between workbooks, so I had to copy and paste my data in the same workbook.

Thanks in advance

SharonMee

Sub filterinfo()
Range("C3").Select
Selection.Copy
Sheets("Sheet2").Select
' I tried recording the macro from one workbook to another, but it didn't work. In order to demonstrate, I copied an example of the data from the Raw data sheet of the January 2004 workook of the Data folder into sheet2 of my working worksheet.
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Sheets("Sheet1").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.AutoFilter Field:=1, Criteria1:="=Hortons", Operator:=xlAnd
'Instead of "Hortons" in custom of the autofilter, I want to filter by any value in C3 of sheet1
Sheets("Sheet1").Select
Range("C4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Selection.AutoFilter Field:=7, Criteria1:="=0010456", Operator:=xlAnd
'Instead of "0010456" in custom of the autofilter, I want to filter by any value in C4 of sheet1
Range("A2:A6").Select
'I only want to select the values corresponding to the filter. In this case I only had 2 rows that matched, row 2 and row 6, not row 2 to row 6
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A20").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("G2:G6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("C20").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("H2:H6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("E20").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("K2:K6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("F20").Select
ActiveSheet.Paste
Range("C3").Select
End Sub