Hi..
Your post lacks a few details.. but you should be able to apply this to what you need..
Basically.. it does the following:
1. Loops through a List of filter values on Sheet1 from A2 to the Last used row in A.
2. Applies each Filter value to Sheet2 for the range (A2:C" & Last used row)... (Header row is in row 2)
3. Displays the result on Sheet 2 and prompts the user to continue loop to next filter value or to quit filtering. (Displaying what the next Filter value is on the Popup)
Just add a CommandButton1 to Sheet1 and put your data as shown below to test and then adapt..
Code:
Private Sub CommandButton1_Click()
Dim Crit As String, Ans As String
Dim i As Long, LastRow As Long, LastRow2 As Long
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LastRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Crit = Sheets("Sheet1").Cells(i, 1).Value
Sheets("Sheet2").Range("A2:C" & LastRow2).AutoFilter Field:=1, Criteria1:=Crit
If Sheets("Sheet1").Cells(i, 1).Row = LastRow Then Exit Sub
Sheets("Sheet2").Activate
Ans = MsgBox("Go To Next Filter Value?" & Chr$(10) & Chr$(10) & "Next Value = " & Sheets("Sheet1").Cells(i + 1, 1).Value, vbYesNo)
If Ans = vbYes Then
GoTo 1
ElseIf Ans = vbNo Then Exit Sub
End If
1
Next i
End Sub
Sheet1
<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 214px"></COLGROUP><TBODY>
</TBODY>
Excel tables to the web - Excel Jeanie Html 4
Sheet2
| * | A | B | C |
| 1 | * | * | * |
| 2 | Key | Description | Data |
| 3 | 1 | aaa | Yes |
| 4 | 2 | bbb | No |
| 5 | 3 | ccc | Yes |
| 6 | 2 | bbb | No |
| 7 | 4 | ddd | Yes |
| 8 | 1 | aaa | No |
| 9 | 3 | ccc | Yes |
| 10 | 6 | fff | No |
<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 191px"><COL style="WIDTH: 159px"><COL style="WIDTH: 97px"></COLGROUP><TBODY>
</TBODY>
Excel tables to the web - Excel Jeanie Html 4
