Pulling data from mutliple sheets into Summary sheet

[DBF]

Hi Excel gurus,

Is there a formula / macro that i can use to pull data from the same cell in 30 sheets into a summary sheet in the same workbook?

I have numerical values in cells L8, L9 and L10 of each of the 30 sheets that I would like to transfer to Columns B, C and D respectively, in my summary sheet.

So for example, sheet 2 titled "bob" looks like this:

L
8 300
9 60
10 56

Sheet 3 titled "Jen" looks like this:

L
8 311
9 71
10 61

I would like these values to appear in the summary as follows:

A B C D
3 Bob 300 60 56
4 Jen 311 71 61

I dont need the name to appear automatically, as can just write it, but if I could transfer the numbers that would be brilliant!

I'm pretty sure this is possible, but have no clue where to start. Any help would be greatly appreciated (and save me A LOT of time).

Thanks a bunch
DBF

 

[anandvarma]

USE Consolidate in data tab,
If you cannot figure out watch the following video on excelisfun youtubechannel by Mike Girvin

http://www.youtube.com/ExcelIsFun#p/search/5/iyziliJuA6E

 

[John Davis]

Hi Excel gurus,

Is there a formula / macro that i can use to pull data from the same cell in 30 sheets into a summary sheet in the same workbook?

I have numerical values in cells L8, L9 and L10 of each of the 30 sheets that I would like to transfer to Columns B, C and D respectively, in my summary sheet.

So for example, sheet 2 titled "bob" looks like this:

L
8 300
9 60
10 56

Sheet 3 titled "Jen" looks like this:

L
8 311
9 71
10 61

I would like these values to appear in the summary as follows:

A B C D
3 Bob 300 60 56
4 Jen 311 71 61

I dont need the name to appear automatically, as can just write it, but if I could transfer the numbers that would be brilliant!

I'm pretty sure this is possible, but have no clue where to start. Any help would be greatly appreciated (and save me A LOT of time).

Thanks a bunch
DBF

Maybe something like this??

Sub DBF()
'
Dim ws As Worksheet
Dim x As Variant
Dim lr As Long

For Each ws In ThisWorkbook.Sheets

lr = Sheets("Summary").Cells(Rows.Count, 2).End(3).Row + 1
'
    If ws.Name <> "Summary" Then

    ws.Select
    x = ActiveSheet.Name
    Range("L8:L10").Copy
    Sheets("Summary").Select
    Range("B" & lr).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("B" & lr).Offset(0, -1).Value = x
    
    End If


Next ws


End Sub