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
Judy DeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
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
0
Judy DeoAuthor Commented:
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.
0
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Hi Judy,

Could you please advise then you need to add new sheet on workbook  and generate the results in newly added sheet
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
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
0
Judy DeoAuthor Commented:
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
0
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Hi Judy,

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

Thanks!
0
NorieVBA ExpertCommented:
Judy

Is it always column F where 'Final Each' is found and column G in the corresponding row where the value you want is found?

If it is try this.
Option Explicit

Sub Summarize()
Dim wsResults As Worksheet
Dim wsSrc As Worksheet
Dim rngDst As Range
Dim Res As Variant

    Set wsResults = Sheets.Add(Before:=Sheets(1))
    
    wsResults.Range("A1:B1").Value = Array("Identifier", "Final Each")
    
    Set rngDst = wsResults.Range("A2")
    
    For Each wsSrc In ActiveWorkbook.Sheets
        If Not wsSrc Is wsResults Then
            rngDst.Value = wsSrc.Range("A2").Value
            Res = Application.Match("Final Each", wsSrc.Columns(6), 0)
            
            If Not IsError(Res) Then
                rngDst.Offset(, 1).Value = wsSrc.Cells(Res, "G").Value
            End If
            Set rngDst = rngDst.Offset(1)
        End If
    Next wsSrc
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsDistinguished Expert - 2017Commented:
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
1
Judy DeoAuthor Commented:
@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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.