MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Looking information in list....... URGENT!!!!!


Posted by Juan GutiƩrrez on November 24, 2001 5:13 PM

I want to obtain all the information into a list who match with the criteria that user select (like a filter), but I need to use a combo box, take the cell link, and then looking for all the records who match with the cell link, and show this records to the user.

For example , I've a list with 10 records in sheet1, in sheet 2 , I've my combo box, ans select one of the options, for this option , in sheet 1, exists 5 records who mathc with this criteria, I nedd to display thos 5 records in sheet 2

Please help me, I can't find the way to do this.

Thanks


Posted by Who knows??? on November 25, 2001 9:06 PM

See if you can't edit this to help you...

I need a macro that will copy a row from one worksheet, to another blank worksheet IF a certain condition is met. Say for example, if on my first worksheet, I have 10 columns of data, and 100 rows, and the user wants to have all the rows with an "X" in the first column to be copied to the blank worksheet.


NOTE - this macro assumes that your first row of data is an header row.


Sub Extract_Data()
'Macro written by Barrie Davidson

'Variables used by the macro
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String

'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Select the first 10 columns and first 100 rows
'(note you can change this to meet your requirements)
Range("A1:J100").Select
'Apply Autofilter
Selection.AutoFilter
'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter condition to be met")
'Filter the data based on the user's input
'NOTE - this filter is on column A (field:=1), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=1, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
'Open a new file
Workbooks.Add Template:="Workbook"
'Get this file's name
NewFileName = ActiveWorkbook.Name
'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original file
Workbooks(CurrentFileName).Activate
'Clear the autofilter
Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("A1").Select

End Sub