Jtdylktuy

I have workbook with multiple spreadsheets. I would like to be able to search through these workbooks to find multiple results.
In work book1 - Called numbers.

• I have column B headings as test names


• I have column c headings as alignment names


• 
  

  What I would like to be able to do using a formula in a new worksheet is create my alignment names as rows, then list all the test names that are assigned to that alignment name through my entire workbook

  
  
  

  (I was not able to show in multiple tables, but each results would need to be in a new cell and the search would be across multiple workbooks.)

  
  

Hi,
Sample data in previous post.
I have written the code as follows:

Dim countColTracking As Integer

Dim countCol As Integer

Dim countRow As Integer

Dim colName As String

Dim Sheetname As String

Dim Stoploop As Boolean

Dim ws As Worksheet

Dim countSheetR As Integer

Dim WsName As String





For Each ws In ActiveWorkbook.Worksheets

    Sheetname = ws.Name

    countRow = 7

    countColTracking = 2

    If Sheetname <> "Sheet1" And Sheetname <> "Infotab" And Sheetname <> "Tracking Sheet" And Sheetname <> "Initialise" And Sheetname <> "ClassList" And Sheetname <> "Template" Then

        While Sheets("Tracking Sheet").Cells(countRow, 1) <> ""

            colName = Sheets("Tracking Sheet").Cells(countRow, 1)

            countCol = 2

            Stoploop = False

            While Sheets(Sheetname).Cells(2, countCol) <> "Total" And Stoploop = False

             WsName = Sheets(Sheetname).Cells(2, countCol)

                If Sheets(Sheetname).Cells(4, countCol) = colName Then

                    Stoploop = True

                End If

                countCol = countCol + 1

            Wend

            If Stoploop = True Then 'Value is found - do something



                Sheets("Tracking Sheet").Cells(countRow, countColTracking) = Sheets(Sheetname).Cells(2, countCol)

                'countColTracking = countColTracking + 1

            End If

            countRow = countRow + 1

        Wend

    End If

Next

But now my loops are in the incorrect place. I retrieve Sm Pg8, but I should retrieve SMpg 7 as in the above layouts.
It then is still looping incorrectly in that it moves straight to the next column "Answer Questions", instead of moving to the next worksheet

Any ideas?

Thank you

The result should look like this: