Consolidate Data from 30 workbook tabs

[Andy0311]

Hello - I have a workbook which contains a report for 30 employees. The reports vary in length. But, the name of the employee and a number (derived from counting)are always in the same cell. The employee's name is always in B5 and the number is always in H4 regardless of the report length. Ideally, rather than going through each tab to get the employee name and the number, I would like to run a script that produces in a new sheet the following:

Amy 12
Joe 15
Cam 15
Jon 13
Max 15

and so forth. ...

The transfer of this data to a master report would be so much easier. Thanks for your help.

Andy

 

[Fluff]

How about

Sub Andy0311()
    Dim Ws As Worksheet
    Dim i As Long
    i = 1
    With Sheets.Add(Sheets(1))
        .Name = "Master"
        For Each Ws In Worksheets
            If Not Ws.Name = .Name Then
                i = i + 1
                .Cells(i, 1) = Ws.Range("B5").Value
                .Cells(i, 2) = Ws.Range("H4").Value
            End If
        Next Ws
    End With
End Sub

 

[Andy0311]

Hi Fluff,

Thanks again for your help. I will run this code as soon as I can. I have the rest of the week off and can't wait to get it installed and run. I will get back with the results. Best wishes, and thanks again.

Andy

 

[Fluff]

Look forward to hearing the results

 

[Andy0311]

Hi Fluff,

I just ran it and it worked flawlessly. Exactly what I need. Another hour saved. Thanks again and again!

Andy

 

[Fluff]

My pleasure & thanks for the feedback