Advanced filter not working
I've a file with data in "Unprocessed" tab. The objective is to filter âUnprocessedâ tab based on the criteria set out in âcriteriaâ sheet and create separate sheets for each criterion, copy paste the filtered data from the âUnprocessedâ tab. The criteria is set out from Row 2 and in Row 1, I've mentioned the Criteria name which would also be the new sheet name. Though the macro runs, it pulls out correct data only for the second criterion and not for other. here is the code....Could someone help pl!
Dim rng1 As Range
Dim crit1 As Range
Dim shname As String
Dim countrange As Range
Dim i As Integer
Dim c As Integer
Dim Newsheet As Worksheet
Sheets("criteria").Select
Set countrange = Sheets("criteria").Range("A1", Sheets("criteria").Range("A1").End(xlToRight))
c = countrange.Columns.Count
If c = 256 Then c = 1
For i = 1 To c
Set Newsheet = Sheets.Add(Type:=xlWorksheet) 'Add new worksheet
Sheets("criteria").Select
Set crit1 = Sheets("criteria").Range(Cells(2, i), Cells(2, i).End(xlDown))
Sheets("Unprocessed").Select
Cells.Select
Set rng1 = Sheets("Unprocessed").Range("A1", Sheets("Unprocessed").Range("A1").End(xlDown).End( xlToRight))
rng1.AdvancedFilter xlFilterInPlace, crit1, , False
Selection.Copy
Newsheet.Paste
Application.CutCopyMode = False
'Sheets("criteria").Select
shname = Sheets("criteria").Cells(1, i).Text
Newsheet.name = shname
'Sheets("Unprocessed").ShowAllData
Next i
'Sheets("Unprocessed").ShowAllData
'ActiveWorkbook.Save
End Sub
|
