How to get results from a different table/sheet that dynamically changes.

[GinoSanPascual]

Hello,

I have tried the search engine and I don't think I have worded my issue correctly. Pardon my English as it is not my first language.

I'll explain what I have and what I need.

What I have.
1. I have a workbook where it contains several sheets.
2. The first sheet will contain results from all the other sheets. Let's call the first sheet as the ReportPage*
3. Lets say the remaining sheets besides the ReportPage* are called Sprint1, Sprint2, Sprint3 and so on.
4. The ReportPage is a dynamic table.
5. The Sprint1 , Sprint2..etc, will contain 2 important Columns Lets name these Columns as Data* - Column A and Results* - Column B
Note : The Data and Results column are related to each other . so Data == Results.
6. The Sprint1, Sprint2..etc sheets, will have a cell that has the name of the sheet. Example : Cell A1 = Sprint 1.

What I need, badly.
1. The ReportPage* should contain the following.

• A Column that will contain the Data
• Columns for every Sprint*(Sprint 1, Sprint 2 sheets. this Sprint Sheets can go upto 20 or more) - In this issue, we can use the Cell A1 = Sprint
• Here's the kicker, For the Column Data* the information should be collected from the Sprint* Sheets, and every Data* this will show the Results on the Sprint* Columns

Example
ReportPage
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Sprint 1 (these are the sheets)[/TD]
[TD]Sprint 2 (these are the sheets)[/TD]
[/TR]
[TR]
[TD]Example Data 1[/TD]
[TD]Results 1[/TD]
[TD]Results 1[/TD]
[/TR]
[TR]
[TD]Example Data 2[/TD]
[TD][/TD]
[TD]Results 2[/TD]
[/TR]
</tbody>[/TABLE]

Sprint 1 sheet
Cell A1 = Sprint 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Results[/TD]
[TD]Notes:[/TD]
[/TR]
[TR]
[TD]Example Data 1[/TD]
[TD]Results 1[/TD]
[TD]blah blah[/TD]
[/TR]
[TR]
[TD]Example Data 3[/TD]
[TD]Results 3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sprint 2 sheet
Cell A1 = Sprint 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Results[/TD]
[TD]Notes:[/TD]
[/TR]
[TR]
[TD]Example Data 2[/TD]
[TD]Results 2[/TD]
[TD]blah blah[/TD]
[/TR]
</tbody>[/TABLE]


Is there a way to do this?
Thank you for the help!

-Gino

P.S Sorry if my question is unclear.
[TABLE="width: 554"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

This procedure assumes that you have established the worksheets with the headers in row one and the data down column A. It further assumes that the headers in the Report sheet are the sheet names for all the sheets containing the data and the results. The code will evaluate each sheet in the workbook one at a time. It will locate the appropriate column on the report sheet by searching for a match to the name in cell A1 of each sheet. It will then evaluate each cell in column a of the data sheets to determine if column B contains data. If column B is not blank, it will find the matching data in column A or the Report sheet to determine the appropriate row for entry, it will then use the column and row information of the Report sheet to copy the data in column B to the appropriate cell on the Report sheet. At least it worked that way in the test set up.

Sub report()
Dim sh As Worksheet, dSh As Worksheet, lr As Long, rng As Range
Dim fCol As Range, col As Long, fRw As Range, rw As Long
Set sh = Sheets("Report")
    For Each dSh In ThisWorkbook.Sheets
        If dSh.Name <> "Report" Then
            Set fCol = sh.Rows(1).Find(dSh.Range("A1").Value, LookIn:=xlValues)
                If fCol Is Nothing Then                    
                    MsgBox "Sheet Name not found on Report Sheet", vbCritical, "ALERT"
                    Exit Sub
                End If
            col = fCol.Column
            lr = dSh.Cells(Rows.Count, 1).End(xlUp).Row
            Set rng = dSh.Range("A2:A" & lr)
            For Each c In rng
                If c.Offset(0, 1) <> "" Then
                    Set fRw = sh.Range("A:A").Find(c.Value, LookIn:=xlValues)
                        If Not fRw Is Nothing Then
                            rw = fRw.Row
                            c.Offset(0, 1).Copy sh.Cells(rw, col)
                        End If
                End If
            Next
        End If
    Next
End Sub