Select specific part number in one excel tab and the other tab changes accordingly for this selected part

[happymacro]

Hi all:

Very new to the VBA. Here is the goal I want to do.

I have to two data set in two excel tabs.

One tab has part attributes for specific part number and the other tab has sales information. So, my question is how can I filter one part number in part attributes tab and the sales information tab also changes to the part that I have filtered in part attributes tab. Which means whatever I filter in part attribute tab and the sales tab changes accordingly.

Thank you for your help.

HM

 

[Yongle]

As requested filter in "Other" sheet to match the part filtered in first sheet
It activates when use clicks on "Other" tab

The VBA is written very simply and assumes
- headers are in row1 in both sheets
- part attributes are located in first sheet of workbook (1)
- part number is in the first column of data in that sheet (1)
- part number column in "Other" is A

If you have any problem adapting it to the layout of your workbook reply with details of the layout of both sheets

Paste in SHEET module of "Other" tab (see + below)

Private Sub Worksheet_Activate()
    Me.AutoFilterMode = False
    Columns("[B][COLOR=#ff0000]A[/COLOR][/B]").AutoFilter Field:=1, Criteria1:=Sheets([B][COLOR=#ff0000]1[/COLOR][/B]).AutoFilter.Filters([B][COLOR=#006400]1[/COLOR][/B]).Criteria1
End Sub

+ right-click sheet tab \ View Code \ paste code into that window \ {ALT}{F11} to go back to Excel

this is designed to work for only ONE part

 

[happymacro]

Hi Yongle:

My main sheet is called "HRDS" and the filter is actually on column A row2. The part attribute sheet is called "part".

So, I followed on what you wrote below and I did something incorrect. Would you rewrite based on what I have stated above?

HM

 

[Yongle]

Goes in sheet module of HRDS

Private Sub Worksheet_Activate()
    Me.AutoFilterMode = False
    Range("A2", Range("A" & Rows.Count)).AutoFilter Field:=1, Criteria1:=Sheets("part").AutoFilter.Filters(1).Criteria1
End Sub

If that does not work tell me about layout of sheet "part"

 

[happymacro]

Hi Yongle:

I used the coding you have and it worked but the other way around.

My main sheet is HRDS and I will filter the part in HRDS sheet and I would love to have the "part" sheet changes accordingly if I choose any part number in HRDS sheet.

The coding here was to do the filter in the "part" sheet and the HRDS sheet changes according to what I filtered in the "Part sheet".

So, should I put this in the module part?

HM

Goes in sheet module of HRDS

Private Sub Worksheet_Activate()
    Me.AutoFilterMode = False
    Range("A2", Range("A" & Rows.Count)).AutoFilter Field:=1, Criteria1:=Sheets("part").AutoFilter.Filters(1).Criteria1
End Sub

If that does not work tell me about layout of sheet "part"

 

[Yongle]

This is the opposite of what you requested in post#1 where you said you wanted to filter in part attribute tab and make the sales tab part "change to the part I have filtered in the parts attribute tab"

Is HRDS is the Sales tab or are there 3 sheets?
Do you want the filter to match in the other sheet irrespective of which sheet is filtered?
Or do you want post#5 request instead of post#1?

 

[happymacro]

Hi Yongle:

Sorry, I want to post # 5 request instead of post# 1 and thank you for your patience.

HM

 

[Yongle]

I want to post # 5 request instead of post# 1

My main sheet is HRDS and I will filter the part in HRDS sheet and I would love to have the "part" sheet changes accordingly if I choose any part number in HRDS sheet.

1. Delete the code from sheet "HRDS"

2. Paste into sheet module of sheet "part"

Private Sub Worksheet_Activate()
    Me.AutoFilterMode = False
    Range("[COLOR=#ff0000]A2[/COLOR]", Range("A" & Rows.Count)).AutoFilter Field:=1, Criteria1:=Sheets("HRDS").AutoFilter.Filters(1).Criteria1
End Sub

3. Above assumes that sheet "part" column A contains the Part Number and headers are in row 2

 

[Yongle]

Code below places filter arrows at the top of all columns in sheet "part"

Private Sub Worksheet_Activate()
    Me.AutoFilterMode = False
   [COLOR=#ff0000] Range("A2").CurrentRegion[/COLOR].AutoFilter Field:=1, Criteria1:=Sheets("HRDS").AutoFilter.Filters(1).Criteria1
End Sub

 

[happymacro]

Hi Yongle:

Thank you very much!!! It worked and very much appreciated your help here.

HM