Insert Rows by number of Dropdown

[Manuel_1]

Hello Guys

I hope you can help me. First i don`t know a lot about VBA and all Makros i used so far have been just recorded ones.

Currently I´m trying to create an Excelfile, where i can choose a specific number from a dropdown menu (that´salready done) and I want, that one row is then copied and inserted into the file as often as the number says.
For example:
I choose "3" in the dropdown in cell B2 and then row 1 from sheet 2 is inserted 3 times into the sheet below row 11.

Till now I didn´t find any Solution which i understood and were able to adjust to my needs.

So I hope you will be able to help me.

Thanks
Manuel

[Mumps1]

Is row 11 the first available blank row in the sheet? In other words, do you want the rows to be copied to the first available blank row each time you select a number form the drop down?

[Manuel_1]

Yes it´s the first available blank. But the row need to be inserted, as below are the final results of all the formulas.

[Mumps1]

Can you attach a copy of your file. It would make it easier to test possible solutions. De-sensitize the data if necessary.

[Manuel_1]

So in the file in B2:B8 is the dropdown. I want that when i for example choose 3 in B2 and 6 in B4, that row 1 from "tabelle3" is inserted 3 times and row 5 from "tabelle3" is inserted 6 times at the position of row 14.

I hope it´s understandable :D

[Mumps1]

How do you determine which row to copy from "tabelle3"? Will you always copy row 1 if you make a selection in B2 and always copy row 5 if you make a selection in B4? Also, do you always want to insert at row 14? Please clarify. It would help if you could manually insert the rows in the Rechner sheet to show what the results would look like. Perhaps you can attach an updated file.

[Manuel_1]

Yes it will be always row 1 for selection in B2, row 2 for selection in B3, row 3 for B4, ... So B2:B8 is related to rows 1-7 in "tabelle3" (as they´re named A-G). Means when i choose like in the attached example, the shown rows are inserted. So 3 times A, 1 times C and 2 times D. Actually the inserted rows are all the same, except of the name in column A. There the names are different. Probably this makes it easier.

Thanks a lot for your help. I really appreciate that.

[Mumps1]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Rechner" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in B2:B8. The macro assumes that there will always be some data in the green merged cells that currently contain the word "Results".

Please Login or Register  to view this content.

[Manuel_1]

Thanks a lot. That works perfect. Is there also a way to reduce the rows in case i choose a lower number after i for example misclicked?

[Mumps1]

You are very welcome. Perhaps the easiest way would be to highlight the rows and delete them manually.

[Manuel_1]

Yes thats how I do it But i also get error 1004 when switching back to 0.

[Mumps1]

Try this macro. When you select zero it will delete the rows.

Please Login or Register  to view this content.

[Manuel_1]

Works perfect now. Thank you so much

[Mumps1]

My pleasure.