I am trying to create a sub that prompts a user for an input, in this case a
number from 1-5, and then takes this value and enters it into the Excel
filter tool. Im trying to get all the records that are listed on each line
to be copied/pasted to a sheet called €œResults€�. I almost have the filter
part working, but not quite. It fails on the following line:

With myCell.CurrentRegion



My code is shown below:

Sub FilterList()
Dim mySht As Worksheet
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer
myShtName = ActiveSheet.Name
Number = InputBox("What number do you want to find?")
Set myArea = ActiveCell.CurrentRegion.Columns(6).Offset(1, 0).Cells
Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = "Results"
With myCell.CurrentRegion
.AutoFilter Field:=6, Criteria1:=Number
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
End Sub



Also, I am trying to set some kind of security so that if the user enters a
€œ1€�, the user would then be prompted for a password and would have to enter
the correct password or the sub would not run (my supervisors idea). I am
still a ways away from achieving this second goal of matching the €œ1€�, €œ2€�,
etc. with the appropriate password€¦ Im thinking of creating five similar,
but different, macros so that if a user enters €œ1€� Excel will search for a
match for the password (embedded in the macro) and determine if the user has
permission to see the results of the filtered list. Can this be done? Can
anyone help me with these two tasks or point me in the right direction?

Regards,
Ryan---