multiple worksheet look up if value on a line is >0 to return result on separate worksheet

[Mattl1976]

I am trying to create a summary page (bill of materials) for items that need to be ordered taking the information from multiple worksheets. Basically if the qty column in any of the worksheets is >0 then we need to populate with the information on the bill of materials page (supplier, part number, description and qty) the last two columns would be check boxes.

Not sure how to drop in the excel sheet to this thread but i basically want to look up say column E from sheet A or sheet B or Sheet C etc and if the value in column E is >0 then input into the values on that line from column B column C column D and Column E into a separate worksheet.

I hope this makes sense??

I would like to think that most things can be done in excel but I have hit a stumbling block on this one.

Anyone out there able to help?

 

[vcoolio]

Hello Mattl1976,

See if the following works for you:-

Sub Test()

        Dim ws As Worksheet, sh As Worksheet
        Set sh = Sheets("Summary") '----> Change sheet name to suit.

Application.ScreenUpdating = False

sh.UsedRange.Offset(1).Clear

For Each ws In Worksheets
        If ws.Name <> "Summary" Then  'Change sheet name to suit.

With ws.[A1].CurrentRegion
             .AutoFilter 5, ">" & 0
             .Offset(1).Columns("B:E").Copy
             sh.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
             .AutoFilter
             End With
       End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code filters Column E in each sheet (except the Summary sheet) for any value greater than zero and then transfers the relevant rows of data, from Column B to Column E only, to the Summary sheet.
The Summary sheet is refreshed each time the code is run.

I hope that this helps.

Cheerio,
vcoolio.