Insert rows based on count and fill rows with adjacent values

Hi everyone,

Hope I can get some help with this. Please see the description below and attached sample workbook.

What I'm trying to do:

What I'm trying to do is create a macro that will count the number of instances the values in Data column I appear, insert rows below the rows with alphanumeric strings containing the value in Summary column A, and then copy the Data column E values into these new rows if the corresponding column I values equal the searched value.

Data

This sheet contains the data that's going to be used to determine the # of rows that need to be inserted, where they are inserted, and what is to be copied into them.

Summary

This sheet contains alphanumeric strings in column A that contain a value found in column I of sheet1

I have attached a sample workbook. Summary and Data sheets include sample data, and the Desired End Result worksheet is effectively the result I'm trying to reach. Please note, actual column E data in the Data worksheet will contain numbers and letters and will be entirely randomized. Also, Summary column A strings will be a bit longer than what is in the sample workbook.

Any help?

Thanks!!

Hi Groovicles,

Try this:

Please Login or Register  to view this content.

Hi xladept,

Thanks very much. Your code appears to work perfectly on the sample workbook. I'll try and apply it to the actual workbook tomorrow. Thanks very much for your help!!

Would it be too much trouble if explained the code to me? Specifically, I'm really get lost on this part:

[CODE][/r = 2: Do Until ws.Cells(r, 1) = "": I = wd.Cells(r, 9)
Do Until wd.Cells(r + n, 9) <> I: n = n + 1: Loop
ws.Range("A" & r + 1 & ":A" & r + n - 1).Insert Shift:=xlDown
r = r + n: n = 0
CODE]

What is r and n represent?

Thanks

Hi Groovicles,

You're welcome and thanks for the rep!

r is the row counter and n is the occurrence count

Please Login or Register  to view this content.

Hi Groovicles,

Sorry to hear that - I suspect the sample is somehow different than the actual workbook - can you post another more representative sample?

The indices that you're asking about are incremented as the routine progresses with the n resetting to zero after each batch.