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

 

[JLGWhiz]

see if this will work for you.

Sub t()
Dim sh As Worksheet, ws As Worksheet, c As Range, fn As Range, col As Long
Set sh = Sheets("Main") 'Edit sheet name
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> sh.Name 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(Rows.Count, col).End(xlUp)(2) = fn.Offset(, 2).Value
                End If
            Next
        End If
    Next
End Sub

 

[excelnoobhere]

see if this will work for you.

Sub t()
Dim sh As Worksheet, ws As Worksheet, c As Range, fn As Range, col As Long
Set sh = Sheets("Main") 'Edit sheet name
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> sh.Name 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(Rows.Count, col).End(xlUp)(2) = fn.Offset(, 2).Value
                End If
            Next
        End If
    Next
End Sub

The code above works but it keeps going forever, it doesn't end at the last page, it keeps restarting over and over .

look at the picture below

 

[JLGWhiz]

If the code was copied to a public code mocule, like module1, It should only run one time. If you have a worksheet change event firing when the changes are made to the sheet then you might neet to add a statement to turn off the events at the beginning of the code and to reset the event trigger at the end of the code. But the code per se that I posted will not run repeatedly of its own accord.

 

[JLGWhiz]

This would turn off event triggers while the code runs and then reset them before the sub ends. Code goes in a public code module.

Sub t()
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 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(Rows.Count, col).End(xlUp)(2) = fn.Offset(, 2).Value
                End If
            Next
        End If
    Next
Application.EnableEvents = True
End Sub

 

[excelnoobhere]

If the code was copied to a public code mocule, like module1, It should only run one time. If you have a worksheet change event firing when the changes are made to the sheet then you might neet to add a statement to turn off the events at the beginning of the code and to reset the event trigger at the end of the code. But the code per se that I posted will not run repeatedly of its own accord.

It's the only code I ha

This would turn off event triggers while the code runs and then reset them before the sub ends. Code goes in a public code module.

Sub t()
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 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(Rows.Count, col).End(xlUp)(2) = fn.Offset(, 2).Value
                End If
            Next
        End If
    Next
Application.EnableEvents = True
End Sub

This worker perfect, thank you!

 

[excelnoobhere]

This would turn off event triggers while the code runs and then reset them before the sub ends. Code goes in a public code module.

Sub t()
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 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(Rows.Count, col).End(xlUp)(2) = fn.Offset(, 2).Value
                End If
            Next
        End If
    Next
Application.EnableEvents = True
End Sub

I have some rows that are blank and its passing information (quantity) from them, how can I make it ignore empty cells that don't contain Item number in my sheets

 

[JLGWhiz]

Make this modification to the code.

For Each c In sh.Range("C2", sh.Cells(Rows.Count, 3).End(xlUp))
    If c <> "" Then
            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(Rows.Count, col).End(xlUp)(2) = fn.Offset(, 2).Value
                End If
    End If
Next

 

[excelnoobhere]

Make this modification to the code.

For Each c In sh.Range("C2", sh.Cells(Rows.Count, 3).End(xlUp))
    If c <> "" Then
            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(Rows.Count, col).End(xlUp)(2) = fn.Offset(, 2).Value
                End If
    End If
Next

Here is the Results that I'm getting. How can I make it skip the sheet "Data" and "Template"
also how can i make it write the quaint at the corresponding row.

 

[JLGWhiz]

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