Copy rows macro 1

[ctopher]

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

 

[brengine]

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.

[tt]
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
[/tt]
FYI, using the "MonoSpaced Text" option within the "Process TGML" (below the Post Message Here window) will keep the table evenly spaced.

Ken

 

[ctopher]

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

 

[brengine]

Here you go.

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

 

[IRstuff]

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

 

[ctopher]

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

 

[brengine]

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

 

[ctopher]

As you see in the pic, row with the 2 in it is copied from 1. I like to have the macro copy row 2 to the next spacing the same as between 1 & 2. I'm not very good at explaining in typing.

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

 

[IRstuff]

I think you need to show a before and after.

TTFN

 

[ctopher]

Image above is after.
This one is before:

thanks

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

 

[johnwm]

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

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:

http://www.essexsteam.co.uk

 

[ctopher]

Thanks johnwm,
Sorry, but I can't get it to work.

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

 

[ctopher]

KenBolen,
I made your macro work. Thank you much!

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

 

[ctopher]

KenBolen,
Your macro works well, but how can I make it copy selected rows and not just the first row selected?
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

 

[brengine]

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

 

[ctopher]

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