Find values from different tabs into one tab according to cell value

[sb003848]

Hello everyone...

I'm hoping that the following explanation to my situation will be clear enough for you al to help me out... Here is what I'm trying to do...

In my Excel document, I have multiple tabs...

TAB1 : Information on the document
TAB2 : This is where I wish to see the results I'm looking for
TAB3 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for January of 2017
TAB4 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for February of 2017
TAB5 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for March of 2017
TAB6 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for April of 2017
TAB7 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for May of 2017
TAB8 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for June of 2017
TAB9 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for July of 2017
TAB10 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for August of 2017
TAB11 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for September of 2017
TAB12 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for October of 2017
TAB13 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for November of 2017
TAB14 : List of employees (names can be repeated multiple times) with 9 different informations (columns B to K) for December of 2017

Each tabs (from 3 to 14) are exactly the same format (each columns have the same descriptions).

In tab2, the same descriptions are found but with one difference. I have added a few lines in order to add a name field (cell C2) with a dropdown of the names of my employees.

My goal is to be able to select a name from the dropdown and have all the data from tab 3 to 14 show up based on that employee's name.

Anyone up for the challenge to help me out ?

 

[StephenCrump]

It can be done ....

... but why don't you just aggregate all the information from Tabs 3 to 14 into a single worksheet, and simply filter by name and/or month as required?

 

[alansidman]

You could try the attached untested (no worksheet to test) code"

Option Explicit


Sub EEInfo()
    Dim ws As Worksheet
    Dim lr As Long
    Dim lr2 As Long
    Dim i As Long


    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "TAB2" Or ws.Name <> "TAB1" Then
            lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            For i = 1 To lr
                lr2 = Sheets("TAB2").Range("A" & Rows.Count).End(xlUp).Row
                If ws.Range("A" & i) = Sheets("TAB2").Range("C2") Then
                    Sheets("TAB2").Range("A" & lr2 & ":K" & lr) = ws.Range("A" & i & ":K" & i)
                End If
            Next i
        End If
    Next ws
    Application.ScreenUpdating = True




End Sub