asked on

Excel pulling data from multiple tabs into single sheet.

Hello experts,

I have a spreadsheet with about 600 tabs and have to pull data from multiple tabs into a single sheet. The information I need is not on the same line/cell in each tab.

I have attached a sample sheet with individual tabs and what the resulting tab should look like. Is there a formula or function that would do this easily or only a VBA code would work. Vlookup will not work since I have to pull the data from multiple tabs. I have to pull this data from 3 such spreadsheets and each spreadsheet has between 450-600 tabs.

Thanks,
Test-file-5.1.18.xlsx

Learn

Hi Judy,

For me Best solution would be Macro, if your ok with that please try this code

I don't think excel function would be possible, let's wait for experts advise. if so

And please make sure always you Result sheet should be first sheet, Attached sample file with coding for reference

Refer Module 1 also Button Added for reference.

Sub Result()
Dim i As Double
Dim Iden As String
Dim Finaleach As Double
Range("A2:B10000").ClearContents   'if you don't want to clear previous content remove this line
For i = 2 To Worksheets.Count
    Worksheets(i).Select
    Cells.Select
    Iden = Selection.Find(What:="Identifier").Offset(1, 0).Value
    Finaleach = Selection.Find(What:="Final Each").Offset(0, 1).Value
    Range("a1").Select
    Worksheets("Result Sheet").Select
    Range("A10000").End(xlUp).Offset(1, 0).Value = Iden
    Range("A10000").End(xlUp).Offset(0, 1).Value = Finaleach
Next
End Sub

Open in new window

Test-file-5.1.18.xlsm

Judy Deo

ASKER

Hi, Thanks for your quick response. I tried to run it but it is showing an error in code for the following Worksheets("Result Sheet").Select

The result sheet does not exist in my original spreadsheet. That was an example of what data should be pulled and how it should show up.

Learn

Hi Judy,

Could you please advise then you need to add new sheet on workbook and generate the results in newly added sheet

Learn

Hi Judy,

Just did slight change on coding, this coding will add a new sheet and rename that as Result and then will export report for you!

Open Moduel1 and run Macro.

Hope this will help, let us know for any further clarification

Sub Result()
Dim i As Double
Dim Iden As String
Dim Finaleach As Double
Worksheets.Add Before:=Worksheets(1)
ActiveSheet.Name = "Result"
Range("A1").Value = "Identifier"
Range("B1").Value = "Final Each"
For i = 2 To Worksheets.Count
    Worksheets(i).Select
    Cells.Select
    Iden = Selection.Find(What:="Identifier").Offset(1, 0).Value
    Finaleach = Selection.Find(What:="Final Each").Offset(0, 1).Value
    Range("a1").Select
    Worksheets("Result").Select
    Range("A10000").End(xlUp).Offset(1, 0).Value = Iden
    Range("A10000").End(xlUp).Offset(0, 1).Value = Finaleach
Next
End Sub

Open in new window

Test-file-5.1.18.xlsm

Judy Deo

ASKER

Hi,

I need cell A2 from each tab - ( this will stay constant in each tab) and the amount corresponding for "Final each in column G from each tab ( this cell changes in each tab)

so once the data is pulled onto one sheet it should look like this

Identifier      Final Each
Menu A        7,975.89
Menu B        2,940.72
Menu C        4,403.33
Menu D        2,368.18

Hope this clears up the confusion.

Thanks

Learn

Hi Judy,

Can you try with my latest coding, hope that will help you, if not let us know

Thanks!

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shums Faruk

Hi Judy,

Here is my version. Click on Update Results and let me know.

Sub SummarizeResult()
Dim ResWs As Worksheet, Ws As Worksheet
Dim LRow As Long, ResLR As Long
Dim Iden As String, FinalPrice As String

'Define Variables
Set ResWs = Worksheets(" Result Sheet")
ResLR = ResWs.Range("A" & Rows.Count).End(xlUp).Row + 1
ResWs.Range("A2:B" & ResLR).ClearContents

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

'Update Result Sheet
For Each Ws In ThisWorkbook.Sheets
    If Ws.Name <> ResWs.Name Then
        LRow = Ws.Range("G" & Rows.Count).End(xlUp).Row
        Iden = Ws.Range("A2").Value
        FinalPrice = Ws.Range("G" & LRow).Value
        ResWs.Cells(Rows.Count, "A").End(xlUp).Offset(1) = Iden
        ResWs.Cells(Rows.Count, "B").End(xlUp).Offset(1) = FinalPrice
    End If
Next Ws
ResWs.Activate
ResWs.Columns.AutoFit
ResWs.Range("A1").Select

'Enable Events
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Open in new window

Kindly note, your result sheet named has extra space " Result Sheet". Change accordingly.
JudyDeo_Test-file-5.1.18.xlsm

Judy Deo

ASKER

@LET there is no code. Thanks for your help.

Norie, yes it is always column F where 'Final Each' is found and column G in the corresponding row where the value is.
I tried your code on my original worksheet and it worked. Thank you so so much. This is going to save me so much time.