Cannot total inventory across multiple worksheets.

Can you attach a sample workbook with couple of sample sheets and a summary sheet where you want to total up inventory?

See attached workbook.
The Macro will execute when you click the Update button.
It will look at each item in the A column of Total Supply Inventory and search for that item in each of the 20 Box sheets.
It will copy cells B:D and add quantities in columns E and F.

Let me know if this does what you want.

Here's the macro.

Sub inventory()

Dim lr, lrtot, a, x, y As Long
Dim TSI, NSN As String

TSI = "Total Supply Inventory"

With Worksheets(TSI)
  lrtot = .Range("A" & Rows.Count).End(xlUp).Row
End With
Worksheets(TSI).Range("B2:F" & lrtot).Select
Selection.ClearContents

Do While lrtot > 1
    NSN = Worksheets(TSI).Range("A" & lrtot).Value
For Each ws In ActiveWorkbook.Sheets
    If ws.Name <> "Individual Items" And ws.Name <> "Total Supply Inventory" Then
        With Worksheets(ws.Name)
            lr = .Range("A" & Rows.Count).End(xlUp).Row
        End With
       Do While lr > 1
        If Worksheets(ws.Name).Range("A" & lr).Value = NSN Then
            Worksheets(ws.Name).Range("B" & lr & ":D" & lr).Copy _
                Destination:=Worksheets(TSI).Range("B" & lrtot & ":D" & lrtot)
            Worksheets(TSI).Range("E" & lrtot).Value = Worksheets(TSI).Range("E" & lrtot).Value + Worksheets(ws.Name).Range("E" & lr).Value
            Worksheets(TSI).Range("F" & lrtot).Value = Worksheets(TSI).Range("F" & lrtot).Value + Worksheets(ws.Name).Range("F" & lr).Value
        End If
        lr = lr - 1
        Loop
     End If
Next ws
    lrtot = lrtot - 1
Loop
      


End Sub

Maybe I didn't understand the requirements.
The way I wrote the macro, it looks at all the items listed on Total Supply Inventory and looks for them on other sheets.
The items you mentioned [Box 19] were not listed on the Total Supply Inventory sheet.

I am not sure how you intend to use this file.
Are you going to add new items to individual Box sheets and expect the Total sheet to find the new items?

Can you not have a master list of all items listed on the Total sheet?

That is amazing. Both lists will work, its just a change in pro Is there a way to draw from a masterlist when I am entering items into the individual boxes? Typically everything uses NSN numbers, so it would be nice to just punch in the NSN number and the total that we have in stock.

Ok, I modified the macro, so now it will find all items from the Box sheets and list all unique items in column A.
Then it will update all the items as before.

Let me know if this works for you.
Macros Used.

Sub inventory()

Dim lr, lrtot, a, x, y As Long
Dim TSI, NSN As String

TSI = "Total Supply Inventory"
Call listall ' This will ist all unique items [NSN] from all Box sheets.
With Worksheets(TSI)
  lrtot = .Range("A" & Rows.Count).End(xlUp).Row
End With
Worksheets(TSI).Range("B2:F" & lrtot).Select
Selection.ClearContents

Do While lrtot > 1
    NSN = Worksheets(TSI).Range("A" & lrtot).Value
For Each ws In ActiveWorkbook.Sheets
    If ws.Name <> "Individual Items" And ws.Name <> "Total Supply Inventory" Then
        With Worksheets(ws.Name)
            lr = .Range("A" & Rows.Count).End(xlUp).Row
        End With
       Do While lr > 1
        If Worksheets(ws.Name).Range("A" & lr).Value = NSN Then
            Worksheets(ws.Name).Range("B" & lr & ":D" & lr).Copy _
                Destination:=Worksheets(TSI).Range("B" & lrtot & ":D" & lrtot)
            Worksheets(TSI).Range("E" & lrtot).Value = Worksheets(TSI).Range("E" & lrtot).Value + Worksheets(ws.Name).Range("E" & lr).Value
            Worksheets(TSI).Range("F" & lrtot).Value = Worksheets(TSI).Range("F" & lrtot).Value + Worksheets(ws.Name).Range("F" & lr).Value
        End If
        lr = lr - 1
        Loop
     End If
Next ws
    lrtot = lrtot - 1
Loop
      
Range("A2").Select

End Sub

Sub listall()

Dim lr, lrtot, a, x, y As Long
Dim TSI, NSN As String

TSI = "Total Supply Inventory"

With Worksheets(TSI)
    Columns("AA:AB").Select
    Selection.ClearContents
    lrtot = .Range("A" & Rows.Count).End(xlUp).Row
End With

If lrtot < 2 Then lrtot = 2
Worksheets(TSI).Range("A2:F" & lrtot).Select
Selection.ClearContents

a = 2
For Each ws In ActiveWorkbook.Sheets

    If ws.Name <> "Individual Items" And ws.Name <> "Total Supply Inventory" Then
        With Worksheets(ws.Name)
            lr = .Range("A" & Rows.Count).End(xlUp).Row
        End With
        If lr = 1 Then GoTo 20
    Worksheets(ws.Name).Range("A2:A" & lr).Copy _
        Destination:=Worksheets(TSI).Range("AA" & a)
    a = a + lr - 1
    End If
20 Next ws

Call ListUniques

With Worksheets(TSI)
    lrtot = .Range("AB" & Rows.Count).End(xlUp).Row
End With
Worksheets(TSI).Range("AB2:AB" & lrtot).Copy _
    Destination:=Worksheets(TSI).Range("A2")
Columns("AA:AB").Select
    Selection.ClearContents



End Sub

I am not clear on what your thoughts are in the last message.
Can you give me specific example with step by step process you are thinking of?

HTML Code: 

Where would you punch in the MSN number? in the Box sheet?

HTML Code: 

Which master list?

Please describe your process clearly, so I can help you.

Ok, so when you have the master list, you can write a Vlookup formula on the box sheets in columns B and C to fill in the information.
If you have a master list then you can also create a drop down menu in column A so user can simply select items from a list.
I prefer using lists instead of user typing in NSN numbers. It helps avoid someone typing in incorrectly [someone might add an extra space in the middle]. Program won't be able to match the items correctly.
If you need further help, let me know.
If your original request has been solved, you should mark this thread "SOLVED" and create a new thread for further help.

1. Click on the * Add Reputation if you think this helped you
2. Mark your thread as SOLVED when question is resolved