Excel - Add new row/increment 1

[Enkrypted]

Hello,

I'm looking for a macro that will automatically add a new row(s) based upon values being presented in a set of columns/rows, when the macro button is clicked after pasting the information in the cell ranges. I have a set amount of columns that data can be pasted into. The range of data can vary between 10-40 rows per column. The information being pasted in these cells will duplicate to other cells. For example:

Columns E, F, G and H is where the information will be pasted. The amount of data being pasted in each of these columns will vary. Once the information is pasted, each cell will duplicate 4 times to the first set of columns (A, B, C and D). It will look like the following:

I would like to increment each line in columns A, B, C and D based upon how much is pasted into the other 4 columns. So if there was another row of information pasted in the E/F/G/H cells, another line would increment and add 4 duplicates of each into the A/B/C/D column/rows.

If any of you Excel experts could help me get a macro that can do this it would be much appreciated. I'm trying to figured this out myself, but kind of stuck on how to do this. TIA!

Enkrypted
A+

 

[SkipVought]

Hi,

What’s the business case for this requirement?

Where are your column headings?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Enkrypted]

Sorry, it's been awhile since I've posted. What do you mean by business case?

As for the column headers, I don't have any specific header information set yet.

Enkrypted
A+

 

[SkipVought]

The reason. What is to be accomplshed after the multiplication of rows happens.

Often there are better ways to accomplish an ultimate goal. A table with multiple sets of data is, in the classical sense, useless. So there’s more. And knowing that might help get you an even better solution than what you’ve envisioned.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Enkrypted]

Gotcha. It needs to have the information duplicated four times as it gets uploaded to a system that will have other applications (4 apps) use it for ID's within each system

Enkrypted
A+

 

[SkipVought]

So you don’t really care about column headings. Just four sets of data.

So the original set of data is on what sheet starting in what row & column.

And the final (*4) result goes to what sheet starting in what row & column?

I assume that this routine will be used again.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Enkrypted]

No column headers will be ignored. Original data will be pasted on sheet 2 starting on A1 and will be duplicated on sheet 1 starting on A1 as well

Enkrypted
A+

 

[SkipVought]

I made a cell with a Range Named NumberOfIterations on a third sheet where you can enter 4...

Sub DuplicateColumns()
    Dim iCount As Integer, lRow As Long
    
    lRow = 1
    
    For iCount = 1 To [[b]NumberOfIterations[/b]]
        With Sheets(2)
            Sheets(1).Cells(1, 1).CurrentRegion.Copy .Cells(lRow, 1)
            lRow = .Cells(1, 1).CurrentRegion.Rows.Count + 1
        End With
    Next
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Enkrypted]

This is definitely what I'm looking for and it does just as you described, however the problem I'm running into is the results are not staying sequential. When I run the macro with the first line entered as such

The results turn out like this

However when adding additional lines under the first

The results become intertwined with each other

Ultimately, I would like to get the results to end up as such

I really appreciate you helping me out with this. Experts like you make this site a great resource!

Enkrypted
A+

 

[SkipVought]

SORT the redults.

Turn on your macro recorder and record the Sort process.

Post your results so we can generalize for your process.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Enkrypted]

Thanks Skip! I was able to get everything sorted as needed. Much appreciated for your assistance!

Enkrypted
A+