Excel VBA Nested Loop Alternative

Question

I am not an advanced user of Excel and am VERY new to VBA. My only programming experience is 2 C# classes in college. That being said, go easy on me ;)

I am working on a team that audits military bases for energy conservation projects. I am trying to revise a workbook that is currently used to document HVAC equipment in all the buildings on the base. Each building has a separate sheet named after the building number. Each sheet uses the same table and format.

My biggest hurdle was creating a Bill of Materials page that would go through each sheet and count all the parts needed to order. Each sheet can have any combination of parts and quantities so I figured the best way was to loop through each item on a master list and count all the instances in each sheet. As such I have ended up with several nested loops which takes a while to run with a test of only 9 sheets. The code works which is most important to me, but I'm hooked now and want to learn how to make it better. I have picked up a book on VBA and plan on looking into arrays and how they might help. I just wanted to see if anyone could give me some pointers based on what I have now.

    Private Sub GenerateBOM_Click()
'generating a bill of materials with data from templated tables on separate sheets. Part order and quantity can change on each sheet. Sheets are named after
'building numbers which could include letters so couldn't find a better way of excluding the summary and data sheets. Wanted to allow for slight table
'structure changes so attempted to locate everything by names.

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim wsBOM As Worksheet
    Dim tblBOM As ListObject
    Dim row As range
    Dim searchRow As range
    Dim rowCount As Long
    Dim partCount As Long
    Dim totalCount As Long
    Dim partQty As Long
    
    Set wsBOM = Worksheets("Bill of Materials")
    Set tblBOM = wsBOM.ListObjects("BOM")
    Application.ScreenUpdating = False
    
        For Each row In tblBOM.ListColumns("Part Number").DataBodyRange.Rows
            rowCount = row.row - tblBOM.HeaderRowRange.row  'getting index of the row being searched. Tried to use ListRow but couldn't figure it out with the overall search
            totalCount = 0
   
            For Each ws In ThisWorkbook.Worksheets  'Loop through all sheets in a workbook
            
                If ws.Name <> "Cover" And ws.Name <> "Building List" And ws.Name <> "Data" And ws.Name <> "Building Template" And ws.Name <> "Parts" And ws.Name <> "Bill of Materials" Then
                
                    For Each tbl In ws.ListObjects  'Loop through all table on a sheet
                    
                        For Each searchRow In tbl.ListColumns("Part Number").DataBodyRange.Rows 'Loop through all part number rows on table
                            partQty = 0
                            partQty = tbl.ListColumns("Qty").DataBodyRange(searchRow.row - tbl.HeaderRowRange.row)  'getting index of the row being searched to find per sheet part qty
                            partCount = (Application.WorksheetFunction.CountIf(searchRow, row) * partQty)
                            totalCount = totalCount + partCount
                            tblBOM.ListColumns("Project Totals").DataBodyRange.Cells(rowCount).Value = totalCount   'writing total to bill of materials sheet at index of searched part number
       
                        Next searchRow
                    Next tbl
                End If
            Next ws
        Next row
    Application.ScreenUpdating = True
End Sub