Looping Dynamic Filter on the basis value from other sheet

D

danpre

Board Regular
Joined
Aug 29, 2011
Messages
58
Hi Team,

I am into learning phase in VBA field and i need support from you in getting code for 1 dynamic criteria.

I have one sheet with the name List in which the below data is there

[TABLE="width: 328"]
<TBODY>[TR]
[TD]Sr#</SPAN>[/TD]
[TD]Emp Name</SPAN>[/TD]
[TD]Code</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]Arvind Mathur</SPAN>[/TD]
[TD]DD612206</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]LJ892001</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]Ittoop Kurian</SPAN>[/TD]
[TD]AALF8002</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]Tanja Mertin</SPAN>[/TD]
[TD]MP269004</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]MP26900Q</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]
Above is the List sheet

Simultanously I have another 3 sheets with the name as Emp Name mentioned above in which i have the below data

[TABLE="width: 203"]
<TBODY>[TR]
[TD]CoCd</SPAN>[/TD]
[TD] Code[/TD]
[TD]Inv #</SPAN>[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]LJ892001</SPAN>[/TD]
[TD]1900017015</SPAN>[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]LJ892002</SPAN>[/TD]
[TD]1900017017</SPAN>[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]DD612206</SPAN>[/TD]
[TD]1900017018</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

Above sheet is Arvind Mathur Sheet
[TABLE="width: 203"]
<TBODY>[TR]
[TD]CoCd</SPAN>[/TD]
[TD] code[/TD]
[TD]Inv #[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]AALF8002</SPAN>[/TD]
[TD]1900017012</SPAN>[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]AALF9002</SPAN>[/TD]
[TD]1900017013</SPAN>[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]AEWP3001</SPAN>[/TD]
[TD]1900017279</SPAN>[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]AEWP3004</SPAN>[/TD]
[TD]1900017280</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

Above sheet is Ittoop Kurian Sheet


and third sheet Tanja Mertin doesn't have any data except header


Requirement- Output

I need to filter the column B (code) in each employee sheet with the value taking from List sheet in column C (Code) and the rest data should be deleted from each employee sheet

After the macro is excured i need the output in below way

[TABLE="width: 203"]
<TBODY>[TR]
[TD]CoCd</SPAN>[/TD]
[TD] Code[/TD]
[TD]Inv #</SPAN>[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]LJ892001</SPAN>[/TD]
[TD]1900017017</SPAN>[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]DD612206</SPAN>[/TD]
[TD]1900017018</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]


Above sheet is Arvind Mathur Sheet


[TABLE="width: 203"]
<TBODY>[TR]
[TD]CoCd</SPAN>[/TD]
[TD] Code.</SPAN>[/TD]
[TD]Inv #</SPAN>[/TD]
[/TR]
[TR]
[TD]8101</SPAN>[/TD]
[TD]AALF8002</SPAN>[/TD]
[TD]1900017012</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

Above sheet is Ittoop Kurian Sheet



I hope you can write the code and provide it to me.

Thanks
 
Sort by date Sort by votes
Try:

Code: 
Sub Test()
    Dim r As Long
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Codes As Variant
    With Worksheets("List")
        For r = 2 To .Range("A1").CurrentRegion.Rows.Count
            If .Range("B" & r).Value <> "" Then
                Set Sh = Worksheets(.Range("B" & r).Value)
                Set Rng = .Range("C" & r)
            Else
                Set Rng = Application.Union(Rng, .Range("C" & r))
            End If
            Codes = Application.Transpose(Rng.Value)
            With Sh.Range("A1").CurrentRegion
                .AutoFilter
                .AutoFilter Field:=2, Criteria1:=Codes, Operator:=xlFilterValues
            End With
        Next r
    End With
End Sub
 
Upvote 0
Hi Andrea,

Thanks for the above code but the above is not executing the following:

* Looping is not done in all the sheets (Autofilter is done in Arvind Mathur sheet only)
* It doesn't autofilter all the codes in list sheet against the respective employee in respective employee sheet together and delete the entire row which is not equal to the code in list sheet against Sheet
of each employee.

Could you help me the VBA code for the above criteria as well.

Thanks
 
Upvote 0
Hi Andrea - It seems that there is some disconnect.

Your codes helps only to autofilter the data which are in the list sheet (column C) and also it loops only in Arvind mathur sheet and not in all other sheet.
I need the VBA code to filter the column B in respective employee sheet against which are not in the list sheet (column C) and delete those data(entire row) from the respective employee sheet.

I hope i am able to clear my requirement properly now.</SPAN>
 
Upvote 0
There certainly is a disconnect. ;)

The code filters the CurrentRegion on Sh, which is the worksheet with the name in column B on worksheet List. Did you copy the exact code that I posted? You didn't mention deleting in your original post.
 
Upvote 0
I mentioned it, see my original comment which says the below comment

Requirement- Output

I need to filter the column B (code) in each employee sheet with the value taking from List sheet in column C (Code) and the rest data should be deleted from each employee sheet


If possible could you please provide me the code for deleting and looping in all other sheet as well.
 
Upvote 0
I'm sorry I wasted your time by completely misreading your original request. Please try:

Code: 
Sub Test()
    Dim r As Long
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim i As Long
    With Worksheets("List")
        For r = .Range("A1").CurrentRegion.Rows.Count To 2 Step -1
            If Rng Is Nothing Then
                Set Rng = .Range("C" & r)
            Else
                Set Rng = Application.Union(Rng, .Range("C" & r))
            End If
            If .Range("B" & r).Value <> "" Then
                Set Sh = Worksheets(.Range("B" & r).Value)
                With Sh.Range("A1").CurrentRegion
                    If .Rows.Count > 1 Then
                        For i = .Rows.Count To 2 Step -1
                            Debug.Print .Range("B" & i).Value & " " & Rng.Address
                            If IsError(Application.Match(.Range("B" & i).Value, Rng, False)) Then
                                .Rows(i).Delete Shift:=xlUp
                            End If
                        Next i
                    End If
                End With
                Set Rng = Nothing
            End If
        Next r
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Display userform from customer shown in specific cell G13
Replies
5
Views
151
ipbr21054
I
I
  • Solved
Load into textbox two values from customer on worksheet
Replies
6
Views
226
AlphaFrog
AlphaFrog
H
  • Question Question
Help filtering a table using LET, UNIQUE functions
Replies
6
Views
210
Dave Patton
D
I
  • Question Question
Unable to run VBA code
Replies
6
Views
362
RoryA
RoryA
R
  • Question Question
Could I ask for VBA (or python) code to create a new workbooks?
Replies
2
Views
216
Radoslaw Poprawski
R

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top