Check for Quantities From Other Sheets

[excelnoobhere]

Hello all,

I have a workbook that contains multiple sheets.
I have my "Main" sheet that contains items and other sheets that contains other information.
on my main sheet the item numbers are located in C, I want to be able to check the rest of the sheets if they contain the following items based on the item number in column C.
The item number in other sheets are located in column B.
If other sheets contain the following items, I want to be able to pass the quantity located in column D to the "Main" sheet starting at U with the title of the sheet on row 1 and so on as many sheets as I have. These sheets and quantities run forever so there is no limit and order are random.

Main sheet titled "Main"
the item number is at C

Other multiple sheets could be titled anything "anything"
The item number is at B

it would look like this

I also have other information between E-T that i don't want to mess with.
I have other sheets titled "Data" and "Template" which I don't want to check for quantities.

Would it be possible to do that after clicking a button because the other sheets are added after?

Any help would be appreciated and thank you in advance

 

[excelnoobhere]

This should take care of the unwanted sheets and where the quantities appear.

Sub t3()
Dim sh As Worksheet, ws As Worksheet, c As Range, fn As Range, col As Long
Application.EnableEvents = False
Set sh = Sheets("Main") 'Edit sheet name
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> sh.Name And ws.Name <> "Data" And ws.Name <> "Template" Then
            If sh.Range("U1") = "" Then
                sh.Range("U1") = ws.Name
            Else
                sh.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = ws.Name
            End If
            For Each c In sh.Range("C2", sh.Cells(Rows.Count, 3).End(xlUp))
                Set fn = ws.Range("B2", ws.Cells(Rows.Count, 2).End(xlUp)).Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    col = sh.Cells(1, Columns.Count).End(xlToLeft).Column
                    sh.Cells(c.Row, "U") = fn.Offset(, 2).Value
                End If
            Next
        End If
    Next
Application.EnableEvents = True
End Sub

You should try to include all of these details in the description of your worksheets in the OP to save a lot of back and forth messages. But then you were probablty not aware that blank cells and other sheets might be a problem in in coding. Now that you know they can be, It is also helpful to know if worksheets use event code, have merged cells or have formulas in ranges being copied from or to, so please consider these items in future posts.
regards, JLG

Sorry about all the mix up, I should have included all these information from the start however it's just like you said, I was not aware that would make a difference. I tried running the last code you sent and this is the results that im Getting.

Where its placing it in the main those are results from sheet -02-. the rest of the sheets are not working and I know these items are in there. refer to picture below:

 

[excelnoobhere]

I do apologize again but those results are coming from the last page, look at the more simple example below:

 

[JLGWhiz]

This tested OK. I had made changes without testing (bad practice) in the previous one.

Sub t4()
Dim sh As Worksheet, ws As Worksheet, c As Range, fn As Range, col As Long
Application.EnableEvents = False
Set sh = Sheets("Main") 'Edit sheet name
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> sh.Name And ws.Name <> "Data" And ws.Name <> "Template" Then
            If sh.Range("U1") = "" Then
                sh.Range("U1") = ws.Name
            Else
                sh.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = ws.Name
            End If
            col = sh.Cells(1, Columns.Count).End(xlToLeft).Column
            For Each c In sh.Range("C2", sh.Cells(Rows.Count, 3).End(xlUp))
                Set fn = ws.Range("B2", ws.Cells(Rows.Count, 2).End(xlUp)).Find(c.Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    Cells(c.Row, col) = fn.Offset(, 2).Value
                End If
            Next
        End If
    Next
Application.EnableEvents = True
End Sub