Hi, Tom and Debra -

Thanks for your responses. I'll give your suggestions a try and let you know.

Thanks again,
Golf


Debra Dalgleish wrote in message ...
Instead of reusing the variables, create unique variables, and show
those on Sheet1 --

'=====================================
Private Sub CommandButton1_Click() 'Search for Comparable units'
Dim strCity As String
Dim strUnit As String
Dim dblMinBr As Double
Dim dblMaxBr As Double
Dim dblMinRent As Double
Dim dblMaxRent As Double
Dim intMinYear As Integer
Dim intMaxYear As Integer

On Error Resume Next
Sheets("Comparability_Data").ShowAllData

strCity = InputBox("Enter The CITY You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=2, _
Criteria1:="=*" & strCity & "*", Operator:=xlAnd

strUnit = InputBox("Enter The UNIT TYPE You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3, _
Criteria1:="=*" & strUnit & "*", Operator:=xlAnd

dblMinBr = InputBox("Enter The MINIMUM BR SIZE Of Unit:")
dblMaxBr = InputBox("Enter The MAXIMUM BR SIZE Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8, _
Criteria1:="<=" & dblMaxBr, Operator:=xlAnd, _
Criteria2:="=" & dblMinBr

dblMinRent = InputBox("Enter The MINIMUM RENT AMOUNT You Are SearchingFor:")
dblMaxRent = InputBox("Enter The MAXIMUM RENT AMOUNT You Are Searching:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6, _
Criteria1:="<=" & dblMaxRent, Operator:=xlAnd, _
Criteria2:="=" & dblMinRent

intMinYear = InputBox("Enter The MINIMUM YEAR BUILT of Unit:")
intMaxYear = InputBox("Enter The MAXIMUM YEAR BUILT Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=1, _
Criteria1:="<=" & intMaxYear, Operator:=xlAnd, _
Criteria2:="=" & intMinYear

Sheets("sheet1").Range("A19:Ac39").Clear
Sheets("Comparability_Data").Range("a1:I16") _
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("sheet1").Range("a19")

'Unload UserForm2
With Sheets("SHEET1")
.Range("A15").Value = "Min"
.Range("A16").Value = "Max"
.Range("B14").Value = "City"
.Range("C14").Value = "Unit Type"
.Range("D14").Value = "Br Size"
.Range("E14").Value = "Rent"
.Range("F14").Value = "Year"
.Range("B15").Value = strCity
.Range("C15").Value = strUnit
.Range("D15").Value = dblMinBr
.Range("E15").Value = dblMinRent
.Range("F15").Value = intMinYear
.Range("D16").Value = dblMaxBr
.Range("E16").Value = dblMaxRent
.Range("F16").Value = intMaxYear
.Range("A1").Select
End With

End Sub
'======================================

golf4 wrote:
Hi, everyone -

I have a quick query that I was hoping to get some help with. I have
created a rent comparability tool, for my employer, that uses the
Auto-Filter to filter a database of housing unit information
("Comparability_Data") using 4 filter criteria, and shows the filtered
results in Sheet1. I have included the code below:

Private Sub CommandButton1_Click() 'Search for Comparable units'
Dim str As String
str = InputBox("Enter The CITY You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=2,
Criteria1:="=*" & str & "*", Operator:=xlAnd

str = InputBox("Enter The UNIT TYPE You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3,
Criteria1:="=*" & str & "*", Operator:=xlAnd

cryMin = InputBox("Enter The MINIMUM BR SIZE Of Unit:")
cryMax = InputBox("Enter The MAXIMUM BR SIZE Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM RENT AMOUNT You Are Searching
For:")
cryMax = InputBox("Enter The MAXIMUM RENT AMOUNT You Are Searching:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM YEAR BUILT of Unit:")
cryMax = InputBox("Enter The MAXIMUM YEAR BUILT Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=9,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

Sheets("sheet1").Range("A19:Ac39").Clear
Sheets("Comparability_Data").Range("a1:I16").Speci alCells(xlCellTypeVisible).Copy
Destination:=Sheets("sheet1").Range("a19")

Unload UserForm2
Sheets("SHEET1").Range("A1").Select
End Sub

My question is how would I modify the code to identify the filter
criteria used so that it would also appear on Sheet1? I was thinking,
maybe, in the footer or somewhere else visible on Sheet1.

Any help would be great --- Thanks!!!

Golf