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

happymacro

New Member
Joined
Nov 4, 2017
Messages
18
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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)
Code:
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

:warning: this is designed to work for only ONE part
 
Last edited:
Upvote 0
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
 
Upvote 0
Goes in sheet module of HRDS
Code:
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"
 
Upvote 0
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
Code:
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"
 
Upvote 0
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" :confused::confused:

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?
 
Upvote 0
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"
Code:
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
 
Last edited:
Upvote 0
Code below places filter arrows at the top of all columns in sheet "part"
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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