Help with VB/Macro

[mumblyjoe]

No doubt it has been asked many times before but if somebody could point me in the right direction I would be grateful. I have a spreadsheet that has two worksheets. The first contails a table of names, the second contains names and additional information.
What I need to happen is: By clicking on a name in the first spreadsheet this will automatically open the second worksheet, but the information displayed must only be for the relevant person.

I.E
By clicking on Joe Bloggs' name in the first worksheet, I would be taken to the second worksheet where the information has automatically been filtered to show only Joe Bloggs' information.

As you have no doubt guessed I am a complete novice when it comes to anything like this, so if you can help in anyway that would be great. Please could you make the instructions as fool proof as possible.

Nice One

 

[Crystalyzer]

1. On the worksheet that contains the names that you want to click on, create a range called "Names" that contains all the names you could possibly want to click on and filter the second sheet by. In my example that is Cells A1:A3. I have named this worksheet "Names"

Excel Workbook
	A
1	Joe Bloggs
2	Biggie Smalls
3	Huey Lewis
Names

Excel 2007

2. On the worksheet that contains the information to be filtered, create a range name called "Data" that contains all the data you want to filter including the column headers. In my example that is cells A2:B14. I included an amount column with a subtotal calculation to show how the amount changes based on the data being filtered. I have named this worksheet "DataSheet"

Excel Workbook
	A	B	C
1			Total Amount
2	Name	Amount	630
3	Joe Bloggs	25
4	Biggie Smalls	30
5	Huey Lewis	35
6	Joe Bloggs	40
7	Biggie Smalls	45
8	Huey Lewis	50
9	Joe Bloggs	55
10	Biggie Smalls	60
11	Huey Lewis	65
12	Joe Bloggs	70
13	Biggie Smalls	75
14	Huey Lewis	80
DataSheet

Excel 2007

Cell Formulas
Range	Formula
C2	=SUBTOTAL(9,B3:B14)

3. Go to the VBE and copy the following code to the worksheet code module of the worksheet that contains the names. You may need to change the name of the worksheet in the code below from "DataSheet" to whatever your worksheet name is that contains the filterable data but that is all you should have to do.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim isect As Range
    
    Set isect = Application.Intersect(Target, Range("Names"))
    
    If Not isect Is Nothing Then
        FilterShowData (Target.Value)
    End If
End Sub

Sub FilterShowData(FV As String)
    With Worksheets("DataSheet")
        .Range("Data").AutoFilter Field:=1, Criteria1:=FV
        .Select
    End With
End Sub

Once you click on a name in the Names worksheet you will be redirected to the DataSheet worksheet and the data will be filtered based on the name you clicked on. See Below.

Excel Workbook
	A	B	C
1			Total Amount
2	Name	Amount	190
3	Joe Bloggs	25
6	Joe Bloggs	40
9	Joe Bloggs	55
12	Joe Bloggs	70
DataSheet

Excel 2007

Cell Formulas
Range	Formula
C2	=SUBTOTAL(9,B3:B14)

 

[zaska]

Hi,

I got compile Error after trying the above steps in Excel 2010. Could you kindly figure out the error?

Thanks for a nice code.

The below line was highlighted in yellow color by VBA editor.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[Crystalyzer]

Sorry about that. The "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" macro should be placed in the "Worksheet" section of the VBE on the worksheet where the name is selected. (i.e. use the dropdown at the top of the page to switch from "General" to "Worksheet" and then use the dropdow to the right of it to select the "Selection Change" event and paste in the code. The other macro can stay in the "General" section.