Loop to duplicate rows on a sheet based on a value

I'm having a tough time with a loop macro to duplicate rows in a sheet a number of times based on the user input numeric value in a column on the row. I have a spreadsheet of standard codes and values that any particular project may use multiples of a given code. I want to scroll down, put a 1, 2, ..., 5 in column K and have the code for a duplicate button then loop through and duplicate the rows the number of times indicated and move on through the rows. In all duplicated rows, I want to delete the items in Rows D:G prior to continuing the loop as this will be different in the duplicate rows from the original.

I've attached a test page showing 8 rows of sample data and my code for a single duplicate (in worksheet VBA Editor). My code currently executes for the first few 1's in column K, but stops duplicating when the loop hits an empty cell in K.

Thoughts?

Thanks in advance.
PatrickDuplicate Macro Sample - 8 Rows only.xlsm

hi Patrick, option, please check attachment, press Run button or run code test

Thanks WaterSev,

Your code is much more complicated than I thought would be necessary,but it works. This sheet is actually an excerpt from a much larger workbook, so its working well but I need the resulting duplicate rows to remain on the original sheet.

Is there a way to modify your code to have the process run and return the resulting duplicated rows on the original sheet?

Thanks so much for your help.

please check attachment

PS. The code does not actually insert rows, it redraws the table to accomodate new rows. Is it ok?

I think it will work. I reviewed it with my lead and it appears that we need to copy columns A and D as well for the entire sheet when you paste the result. Additionally, we need to clear column H for the duplicates.

Thanks for your help, I'm definitely over my head here. If you add a couple of comments I may be able to change these type of things on my own?

Thanks again!
-PW

this part:

Please Login or Register  to view this content.

is responsible for columns in duplicate rows.
1 stands for column B
2 stands for column C
7 stands for column H

please check attachment, column A has been added together with some comments on key points

In order to copy Columns A, D, and H as well as the original ones, i modified the code to look like what is shown below. Expanded Range to include A, and modified numbers in Array to include these in the copy range.

Scope creep here, some of these values are actually the outcome of conditional formatting and the code is only copying values and recreating the values. Is there a way to have the formula and formatting of the copied cell be applied in the result? I deleted the .Value on the result line but that did not give me the formulas (thought it would be like paste values). Thanks.

Please Login or Register  to view this content.