Excel vb Filter
Posted by Joseph Was on April 18, 2001 3:47 PM
Return row which in one col. has a 7 digit code with 1,000's of possible codes, filter by a wild card filter.App.InputBox gets search criteria, like: UB?????. Look in code col. then return row's from sheet which meets criteria on a new sheet.
Each time the Filter(SourceArray:=Scode, Match:=Temp, include:=True, compare:=vbTextCompare)=Stest gives miss-match data type, also tried AdvancedFilter, named ranges, explicit ranges, subs, ect. can't get it to work?
Posted by David Hawley on April 18, 2001 8:52 PM
Hi Joseph
Try this code, you will need to modify it to suit your needs.
Sub FilterAndCopy()
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''
'Filter data and copy to new sheet.
'''''''''''''''''''''''''''''''''''
'Add new sheet
Sheets.Add().Name = "NewSheet"
'If "NewSheet" already exists then delete the new sheet.
Application.DisplayAlerts = False
If ActiveSheet.Name <> "NewSheet" Then ActiveSheet.Delete
Application.DisplayAlerts = True
'Filter down the list by Column D _
to meet the Criteria "UB?????"
'Copy this to the New sheet.
With Sheet1
.AutoFilterMode = False
.Rows(1).AutoFilter
.Rows(1).AutoFilter Field:=4, Criteria1:="=UB?????"
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("NewSheet").Cells(1, 1)
Application.CutCopyMode = False
.AutoFilterMode = False
End With
End Sub
Dave
OzGrid Business Applications
