Using a Macro to search though a database and display the results on a new sheet

**tmbg99** · 02-05-2013, 07:51 PM

Hi everyone! I'm so glad I found this forum as I am very new to Excel macros and I'm trying to create something that will help me and my company.

The below thread talks about something very similar to what I am trying to do:

http://www.excelforum.com/excel-prog...-userform.html

I have a 9 column, thousands-of-rows data file and I am trying to pull information from. What I would like to do:

*enter a string (i.e. 'gk') to be searched for in column B
* enter a date range, where the dates are in column D, that you want to filter (i.e. start date 1/1/13, end date 1/31/13
*display all columns in that row that contains the above criteria, on to a new sheet (i.e. I would like the entire row that contains 'gk' in column B and a date from column D within the range entered)

The link above is very close to what I am looking for, except it a)doesnt have a date range and b) it displays results in a userform rather than a new sheet.

Any help would be greatly appreciated!! Once I see the code for it, I can learn off of that to do other things.

**p24leclerc** · 02-05-2013, 10:45 PM

here is a quick and dirty macro that would do it:

Public Sub Pull_Data()
'
Dim Text_Q As String, Date_S As Date, Date_E As Date
Dim Data_R As Range
Sheets("Sheet1").Select
Text_Q = InputBox("Enter text to search in columnB", "FILTER DATA - COLUMN B")
Date_S = InputBox("Enter start date", "START DATE")
Date_E = InputBox("Enter end date", "END DATE")
Set Data_R = Range("A1", Cells(Rows.Count, 1).End(xlUp)).Resize(, 9)
Range("D2").Select
Selection.AutoFilter
ActiveSheet.Range("A1", Cells(Rows.Count, 9).End(xlUp)).AutoFilter Field:=2, Criteria1:="=" & Text_Q, _
    Operator:=xlAnd
ActiveSheet.Range("A1", Cells(Rows.Count, 9).End(xlUp)).AutoFilter Field:=4, Criteria1:= _
    ">=" & Date_S, Operator:=xlAnd, Criteria2:="<=" & Date_E
Columns("A:I").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A2").Select
Selection.AutoFilter
End Sub

**tmbg99** · 02-07-2013, 12:16 PM

Thanks Pierre! I'm running this and it is giving me the error: "Run time error 1004 autofilter method of range class failed". I am trying to fix this but am having no luck. Is there something I am overlooking?

**tmbg99** · 02-07-2013, 12:18 PM

Thanks Pierre! I'm running this and it is giving me the error: "Run time error 1004 autofilter method of range class failed". I am trying to fix this but am having no luck. Is there something I am overlooking?

**p24leclerc** · 02-07-2013, 05:34 PM

it might be the way you enter your date in the input boxes. It has to do with your system setup.
There are lots of way for date format: 2013-01-25 or 2013/01/25 or 01/25/2013 etc.
Try to run the macro step by step and see where it bugs.

Using a Macro to search though a database and display the results on a new sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using a Macro to search though a database and display the results on a new sheet

Re: Using a Macro to search though a database and display the results on a new sheet

Re: Using a Macro to search though a database and display the results on a new sheet

Re: Using a Macro to search though a database and display the results on a new sheet

Re: Using a Macro to search though a database and display the results on a new sheet

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions