Getting data from a big list into several smaller lists, based on criteria
Posted by Chris Rock on July 20, 2001 8:09 AM
I have a workbook with several sheets. The first sheet is for raw data, which we'll call CALLS. Each record in this table has a SUBJECT field.
The other sheets are named for the types of SUBJECTS. For example, I have a MICROSOFT OFFICE worksheet, where records with SUBJECTS like Word, Excel, Powerpoint, and Frontpage would go.
The question is: How do I (using formulae or VBA) pull or copy all the records in my CALLS table from my raw data sheet that have a specific subject. This would be almost like an advanced filter, except I need the results on different sheets. I guess I can use a macro to do several Advanced Filters against my data and then copy (or move) the results to different sheets. Would this work?
I usually have between 10000 and 12000 records in my CALLS table. I can put the list of SUBJECTS that belong on each sheet anywhere. I'd like to have them on the sheet that the records would get copied to.
Posted by Sid on July 21, 2001 3:35 PM
Trying to do this also - part way there
Chris,
this may help you,its a modified version of tip44 on MrExcels site to search thro' various worksheets & paste into a report sheet using various search criteria.
Sheets contain lists with colums containing boat name, date fitted, date removed, where fitted.
I can get results OK for the 1st 3 items, but it all comes unstuck when trying to match the FittingValue (where fitted). This column contains cells formatted to general and cotain descriptions such as P1, M3 ,25M , etc.
Maybe you can get it to work for you
Can anyone see where I am going wrong.
Any help much appreciated
Thanx
Sid
Public Sub SearchByFitting()
Application.ScreenUpdating = False
Sheets("Report").Select
FinalRow = Range("A65536").End(xlUp).Row
Range("A3", "L" & FinalRow).Select
Selection.ClearContents
Sheets("AA").Select
FinalRow = Range("A65536").End(xlUp).Row
For x = 3 To FinalRow
Boatvalue = Range("C" & x).Value
DateInValue = Range("I" & x).Value
DateOutValue = Range("K" & x).Value
FittingValue = Range("F" & x).Value
If FittingValue = P2 And Boatvalue = 17 And DateInValue <> "" And DateOutValue = "" Then
'the values about will be linked to a search form if i ever get this to work !
Range("A" & x & ":L" & x).Copy
Sheets("Report").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
Sheets("AA").Select
End If
Next x
Application.CutCopyMode = False
Sheets("Report").Select
FinalRow = Range("A65536").End(xlUp).Row
NextRow = Range("A65536").End(xlUp).Row + 1
If FinalRow < 3 Then
' This pastes a message "no matches found" (in cell P2)- which stops the column header (row 2) being overwritten on the next search if no results are found
Range("P2").Copy
Range("A" & NextRow).Select
ActiveSheet.Paste
End If
Range("A1").Select
End Sub
