Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
search worksheets in different workbooks
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
search worksheets in different workbooks
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 -- Regards Frank Kabel Frankfurt, Germany 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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search multiple workbooks | Excel Worksheet Functions | |||
Search across multiple workbooks | Excel Discussion (Misc queries) | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
search through workbooks | Excel Worksheet Functions | |||
Search and Compare two Workbooks | Excel Programming |