IF A>0 on [MULTIPLE WORKSHEETS] THEN copy row to WORKSHEET:BOM

[PlugSmart]

I have developed the following workbook to assist with estimating for my company. As the business has grown, so has the number of items in each worksheet. I use conditional formatting to highlight the cells where the quantity is greater than zero so the materials do not get lost in such large worksheets. However, it would be helpful if I could build a macro that would look at the entire workbook and copy any row that has a Cell A value greater than 0 (ignoring text) to create a comprehensive bill of material, with the bill of material separated by a header for the page where the materials were copied from. Please let me know if you can help. Thank you.

 

[mrshl9898]

See how this goes. Just add the sheets you want to skip to the IF AND section

Sub Copydata()

Dim wbk As Workbook
Dim sht As Worksheet
Dim ss As Worksheet
Dim Last_Row As Long
Dim Last2_Row As Long

Application.DisplayAlerts = False

For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Summary" Then
        Worksheets(i).Delete
    End If
Next i

Sheets.Add.Name = "Summary"
Set ss = Sheets("Summary")
Set wbk = ThisWorkbook
Sheets("Summary").Rows(1).Copy ss.Rows(1)

    For Each sht In wbk.Sheets
            If sht.Name <> "Summary" And sht.Name <> "Summary" Then  ''may need to add more or remove totals here, sheets you want to exclude
                Last_Row = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
                    For i = 2 To Last_Row
                        If sht.Cells(i, 1).Value > 0 Then
                            Last_Row2 = ss.UsedRange.Rows(ss.UsedRange.Rows.Count).Row + 1
                            sht.Rows(i).Copy ss.Cells(Last_Row2, 1)
                        End If
                    Next i
            End If
    Next sht
    
Application.DisplayAlerts = True

End Sub

 

[PlugSmart]

Not able to get this macro to work. Please explain further. Thank you.