Creating lists from multiple sheets.

[DylanMa]

Hi,

I am wanting to know if I can in VBA;

Have a button that when it is clicked;

Searches the name written in a drop down box, the name is then located on another sheet column 1 of "Breakfast" the sheet name of the sheet from which I want the data is located in column 54 of the same page.

Once it has the sheet name I need it to search for the data on sheets("Name obtained from dropdown box").Range(A11:A35) but not include blank rows. and then add it to the sheets("Shopping List") Column 1 row 2.

Any help would be greatly appreciated.

Dylan

 

[mart37]

strDropDown = Sheets("Sheet11").DropDowns("Drop Down 1").List(Sheets("Sheet11").DropDowns("Drop Down 1").ListIndex)
strSheet = Sheets("Sheet2").Columns(1).Find(strDropDown, , xlValues, xlWhole).Offset(, 1)
RowNumber = 2
With Sheets(strSheet)
    For Each Row In .Range("A11:A35").Rows
        If Application.CountA(Row.EntireRow) <> 0 Then
            Row.Copy Destination:=Worksheets("Sheet4").Range("A" & RowNumber)
            RowNumber = RowNumber + 1
    End If
    Next
End With

Change the references for your workbook.