Get row from different sheets based on a string

Question

I have multiple sheets, to track the # of raffle tickets for each person.

Main sheet:

|-----------|------------------|
|    Name   |     # of tickets |
|-----------|------------------|
|   Adam    |         5        |
|-----------|------------------|
|   Michael |         2        |
|-----------|------------------|
|   Sara    |         4        |
|-----------|------------------|

Sheet 1:

|-----------|------------------|---------------|
|    Name   |     Activity     | # of tickets  |
|-----------|------------------|---------------|
|   Adam    |  Registration    |        2      |
|-----------|------------------|---------------|
|   Michael |  Registration    |        2      |
|-----------|------------------|---------------|
|   Sara    |  Registration    |        2      |
|-----------|------------------|---------------|

Sheet 2:

|-----------|------------------|---------------|
|    Name   |     Activity     | # of tickets  |
|-----------|------------------|---------------|
|   Adam    |  Registration    |        2      |
|-----------|------------------|---------------|
|   Sara    |  Registration    |        2      |
|-----------|------------------|---------------|
|   Adam    |  Extra ticket    |        1      |
|-----------|------------------|---------------|

Question: How do I make so that Main Sheet is updated automatically based on Sheet 1, Sheet 2... Sheet n? So basically the Main Sheet should grab the row of each Sheet based on the Column "Name" and then fetch the value of # of tickets and sum it in main sheet.

Answer 1

To create code blocks macro vba

Sub GetInfoSheet()

    Dim WS_Count As Integer
    Dim I As Integer                             
    Dim SumAdam As Integer
    Dim SumMichel As Integer
    Dim SumSara As Integer

    WS_Count = ActiveWorkbook.Worksheets.Count

    For I = 2 To WS_Count
        strTest = ActiveWorkbook.Worksheets(I).Cells(3, 3)
        SumAdam = SumAdam + CInt(strTest)
        strTest = ActiveWorkbook.Worksheets(I).Cells(4, 3)
        SumMichel = SumMichel + CInt(strTest)
        strTest = ActiveWorkbook.Worksheets(I).Cells(5, 3)
        SumSara = SumSara + CInt(strTest)

    Next I

    With ActiveWorkbook.Worksheets(1)
        .Cells(3, 3).Value = SumAdam
        .Cells(4, 3).Value = SumMichel
        .Cells(5, 3).Value = SumSara

    End With
End Sub

Answer 2

Replied your data like this: Main Sheet and 2 sheets of data:

In my MainSheet, the formula've used in B1 is:

=SUMIF(Sheet1!$A$1:$A$3;A1;Sheet1!$C$1:$C$3)+SUMIF(Sheet2!$A$1:$A$3;A1;Sheet2!$C$1:$C$3)

You will need a SUMIF for each sheet you want to add.

Function SUMIF