×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-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!

*Eng-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.

Students Click Here

Copy rows macro

Copy rows macro

Copy rows macro

(OP)
I'm not new to Excel, but macros are somewhat new to me.
I have a spreadsheet with rows that have cells with specific data in them. How can I create a macro that will copy a row, select the first cell that has a number for the row number, then paste the row two rows below with the next row number? I hope I made some sense.
thank you
example:
1  (data)  (data) etc


2  (data)  (data) etc


3  (data)  (data) etc

Chris
Sr. Mechanical Designer, CAD
SolidWorks 05 SP2.0 / PDMWorks 05
ctopher's home site
FAQ371-376
FAQ559-1100
FAQ559-1091
FAQ559-716

RE: Copy rows macro

Chris,

No that really didn't make sense. Please use a better example. Maybe take the below and give us a before and after view.


     A     B     C     D     E     F     G
1   xxx   xxx   xxx   xxx
2   xxx   xxx   xxx   xxx
3   xxx   xxx   xxx   xxx
4   xxx   xxx   xxx   xxx
5
6
7

FYI, using the "MonoSpaced Text" option within the "Process TGML" (below the Post Message Here window) will keep the table evenly spaced.

Ken

RE: Copy rows macro

(OP)
KenBolen,
What you show is good. I would like to copy a row (row 1) then have the macro place the copied row below (row 2) ... two rows below row 1, and so on.
thanks

Chris
Sr. Mechanical Designer, CAD
SolidWorks 05 SP2.0 / PDMWorks 05
ctopher's home site
FAQ371-376
FAQ559-1100
FAQ559-1091
FAQ559-716

RE: Copy rows macro

Here you go.

CODE

Sub CopyRowDown2()
    'Store current selected cell
    Dim InitialActiveCellRow As Integer
    Dim InitialActiveCellColumn As Integer
    InitialActiveCellRow = ActiveCell.Row
    InitialActiveCellColumn = ActiveCell.Column
    
    'Copy/paste row containing Active Cell
    ActiveCell.EntireRow.Copy
    Rows(InitialActiveCellRow + 2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    'Select initial cell
    Cells(InitialActiveCellRow, InitialActiveCellColumn).Select
End Sub

RE: Copy rows macro

Are you trying to invert the order?

If so, then you simply need to have an index column and sort the index column for decreasing values.

TTFN

RE: Copy rows macro

(OP)
KenBolen,
Thank you! It works great except when the row number copies, it takes the cell above it. So if row one has "1", then it copies "1" each macro run.
looks like:
     A     B     C     D     E     F     G
1   xxx   xxx   xxx   xxx
1   xxx   xxx   xxx   xxx
1   xxx   xxx   xxx   xxx
1   xxx   xxx   xxx   xxx
 How can I fix it?
thank you

Chris
Sr. Mechanical Designer, CAD
SolidWorks 05 SP2.0 / PDMWorks 05
ctopher's home site
FAQ371-376
FAQ559-1100
FAQ559-1091
FAQ559-716

RE: Copy rows macro

The numbers on the left are headings for the rows, they don't change (just like the column heading letters across the top). Only cell contents change, like cell A1 or D4 or whatever. In the example above these cells are the "xxx" cells.

Please give an example that shows the before and after layouts because I am not understanding.

Ken

RE: Copy rows macro

I think you need to show a before and after.

TTFN

RE: Copy rows macro

Looks like you want to just insert some blank rows between the existing stuff. If that is the case, then try this:

CODE

For a = 2 To 30 Step 3
    Rows(a & ":" & a + 1).Select
    Selection.Insert Shift:=xlDown
    Next
This will take the top 10 rows and space them as shown in your image. You will need to change the Step value and the "a + 1" value if you want more rows in between, and change the 30 to 3 times the number of rows you want to space out.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

UK steam enthusiasts: www.essexsteam.co.uk

RE: Copy rows macro

ctopher,

ActiveCell.EntireRow.Copy -->Will copy the 1st selected row only
Selection.EntireRow.Copy -->Will copy the entire row(s) of all the preselected cells

Where and how to paste them (especially if the selected cells aren't in adjacent/continuous rows) will be the difficult portion, or at least time consuming to write/code. I'd like to help, but I don't have enough free time available to figure that out right now.

Ken

RE: Copy rows macro

(OP)
Thank you. You were a big help. I don't expect you to write a complicated macro, just to steer me in the right direction. I do appreciate your help. I have a book to learn the macros and have been reading help. As we all know, it can be complicated and a lot to learn.

Chris
Sr. Mechanical Designer, CAD
SolidWorks 05 SP2.0 / PDMWorks 05
ctopher's home site
FAQ371-376
FAQ559-1100
FAQ559-1091
FAQ559-716

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 Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

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

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

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

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close