Inserting different tables by changing cell value

[ninny_]

I have lot of tables for contracts with names of products and prices. Depending of contract (on sheet contracts) i choose i need to insert table from sheet with the name of that contract. It would be ideal when it would be posible to somehow make dropdown list of that products and than insert only chosen name and price on sheet order (on the yellow part). I really dont have idea how to do it, or is it possible to do that..

[Mumps1]

On you "order" sheet you have 10 rows highlighted in yellow. On the sheets with the name of that contract, you have more than 100 rows. Do you want to insert all the rows from the sheets with the name of that contract or just 10 rows? If just 10 rows, which rows?

[ninny_]

If it is posible to be drop down list 10 rows would be enough.. and if not i would insert all rows from the sheet with the name of contract.

[Mumps1]

It is possible to insert 10 rows but I need to know which 10 rows from the sheets with the name of that contract. Do you want rows 3 to 12 or rows 4 to 13 …….. or rows 121 to 130?

[ninny_]

Maybe i didnt explain myself good enough.. if it could be in that yellow part a drop down list in every row .. or some popup table..and than to choose from table of contract.. if not that way i would insert the whole table.. ofcourse than it wouldnt be 10 rows.
Thank you for your answers...

[Mumps1]

What items would you want in the drop down list in every yellow row? We could put a drop down list of the sheets in cell C30 to select the sheet.

[ninny_]

i thought something like that.. just i put the command button
Private Sub CommandButton1_Click()
Sheets(Range("c30").Value).Visible = True
Sheets(Range("c30").Value).Activate
End Sub
to select the sheet..

[Mumps1]

You don't need a command button. The sheet can be chosen automatically by clicking on a drop down in C30. That won't be a problem. I just need to know which 10 rows from each sheet you want to copy. If you want all the rows, that's not a problem either.

[ninny_]

Depends what someone wants to order.. maybe just one row, maybe 10... so i cant know wich rows.. maybe to have checkboxes in every row.. than copy selected?

[Mumps1]

Try the attached file. I have unmerged the merged cells in the order sheet. You should avoid using merged cells because they create problems for Excel macros. Click on the drop down in C30 to select a sheet. You will notice I have added another column named "Select (x)". Simply place an "x" (lowercase) and exit the cell. That information will be copied to the order sheet. In this manner the user can choose any row up to 10 rows.

[ninny_]

This is great... thenk you very much!!

[Mumps1]

You are very welcome.