AboutBob Umlas Expertise I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...)
Experience Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."
Publications Excellence, The Expert, Microsoft
Awards and Honors MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks
Help please, my first post to one of these facilities, normally I struggle till I find a solution but this one is beating me and I am running out of time to fix the problem!
I have a complex workbook compiled from 200 individual xls worksheets that is updated monthly, each sheet is set up with the same format but contains different serial number text data in cells c14:c200, e14,e200 and f14:200 an example might be "62606" in c21, "2000562" in e21 and "6553006" on "sheet56" Some of these sheets may only use cells c14:f24 etc whilst others use the full c:14:f200
Each month I add a new data lookup sheet as "DataLookup" and the data I want to search for is in column A with the range between say a1:a6500.
What I need to do is create a macro that takes the value in the first cell, say a1 contains "6553006" and searches each of the 200 worksheets.
For every exact match it finds, it copies the entire row data to a new worksheet that also includes the "sheetname" it found it on in column A, carries on searching for any other matches and adds those to the new "FoundData" worksheet. Once that search is complete it does the same for the data in a2 etc right through to the end of that range. Normally the data will only be found on one or two individual worksheets for each lookup
The range of data I need to match varies from month to month say from 5500 entries to lookup to 6500 and the matched data might be found in any of the 3 columns.
I am using excel 2003.
Hope that is enough info to go on.
Thanks in advance
Dave
ANSWER: Sub Finder()
Dim Rg As Range, rws As Integer
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("FoundData").Delete
Set fnd = Worksheets.Add
fnd.Name = "FoundData"
For i = 1 To Sheets("DataLookup").Range("A65536").End(xlUp).Row
For Each sh In Sheets
If LCase(sh.Name) = "founddata" Or LCase(sh.Name) = "datalookup" Then GoTo NextSh
Err.Clear
Set Rg = Nothing
Set Rg = sh.Cells.Find(Sheets("DataLookup").Cells(i, 1).Value, lookat:=xlWhole)
If Err.Number = 0 And Not Rg Is Nothing Then 'found
rws = rws + 1
sh.Rows(Rg.Row).Copy fnd.Cells(rws, 1)
Application.CutCopyMode = False
fnd.Cells(rws, 1).Insert xlToRight
fnd.Cells(rws, 1).Value = sh.Name
End If
NextSh:
Next
Next
End Sub
---------- FOLLOW-UP ----------
QUESTION: Thanks Bob
I have modified your solution to colour code matched cells on the DataLookup sheet yellow using the interior.colourindex = 6 command, but would also like to colour code the corresponding found cell on the Founddata sheet to interior.colourindex = 7, at the moment I can only get the whole row to colour. Is there an easy way of achieving this? Running this macro process for 5000 entries also seems quite slow, is it possible to speed it up at all, as it is currently taking more than 30 minutes and freezes Excel, whilst it runs.
Thanks in advance
Dave
Answer I'd have to see your modified code to color the cells. You can probably speed this up by putting this statement at the beginning of the macro (after "Sub"):
Application.Calculation=xlcalculationManual
and this before End Sub:
Application.Calculation = xlcalculationAutomatic
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
