INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel - Add new row/increment

Excel - Add new row/increment

(OP)
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+

RE: Excel - Add new row/increment

Hi,

What’s the business case for this requirement?

Where are your column headings?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel - Add new row/increment

(OP)
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+

RE: Excel - Add new row/increment

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel - Add new row/increment

(OP)
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+

RE: Excel - Add new row/increment

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel - Add new row/increment

(OP)
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+

RE: Excel - Add new row/increment

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

CODE

Sub DuplicateColumns()
    Dim iCount As Integer, lRow As Long
    
    lRow = 1
    
    For iCount = 1 To [NumberOfIterations]
        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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel - Add new row/increment

(OP)
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+

RE: Excel - Add new row/increment

SORT the redults.

Turn on your macro recorder and record the Sort process.

Post your results so we can generalize for your process.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel - Add new row/increment

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

Enkrypted
A+

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close