How can I pull Multiple information from a Data Sheet Closed

Question

Hello, 
     I have a data sheet with hundreds of Packaged Products that have specific items in each one, and what I am trying to do is on another sheet based on the chosen item it pulls the information that relates to that Item. Here is what it kind of looks like

-----------A------------B-----------C-----------D----------E-------F           
1.----------------2X4 STUDS--4X4 POST--6X6 RU--8X8 RU--WALK IN

2.-20X30 BARN-------0-----------4-----------0----------2--------1

3.-20X40 BARN-----119----------5-----------0----------2--------1     

4.-30X30 BARN-----200----------8-----------1----------2--------1

5.-30X40 BARN-----400---------16-----------2----------2--------0

6. ETC


This is on the Data Sheet, then on the Order Sheet if the 20x30 BARN is chosen then it pulls all the items that apply to that specific product.  

So whichever product is chosen on the Order Sheet then it will pull the applicable information from the Data Sheet. 

Ex. 
20x30 BARN is chosen then it will find the 20x30 BARN row and pull only the information that has a value that is above zero. So it will display the amount plus the Column Header.

Ex.
20x30 BARN 4 4x4 POST  2 8X8 RU 1 WALK IN 

This will be all that will be displayed on the Order Sheet.

Any help will be deeply appreciated.

vcoolio · Answer

Hello SMD,

Looks like the fish are not biting! So, let's see if this code helps:-

Sub FindIt()

Application.ScreenUpdating = False

Dim lRow As Long

Sheets("Data").Select

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lRow)
    If cell.Value = Sheets("Order").Range("B1") Then
    cell.EntireRow.Copy
    Sheets("Order").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If
Next cell

Sheets("Order").Select

For Each cell In Range("A2:F" & lRow)
If cell.Value = 0 Then
cell.Value = ""
End If
Next

Sheets("Order").Range("B1").ClearContents
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Order").Select

End Sub


I've attached a mock-up test work book for you to peruse here:-

https://www.dropbox.com/s/jk0d00qos85t25d/SimpleMindedDwight.xlsm?dl=0

In order to keep the code as simple as possible, you'll note in the test work book that both the "Data" and "Order" sheets are set up the same with the "Order" sheet clearly showing what each package holds. Perhaps, for the sake of simplicity, you could adapt the same method to your work book. 
You'll also note that I've taken the liberty to introduce an "Item ID" in Column A. Type this ID in the search box (B1) in the "Order" sheet to find the relevant item in the "Data" sheet (I just got lazy and couldn't be bothered typing in the whole item description, hence the shortened ID description). If you don't need it, it can be removed.

I hope that this helps.

Cheerio,
vcoolio.

How can I pull Multiple information from a Data Sheet

1 response

Similar discussions

Subscribe To Our Newsletter!