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
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
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
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
CODE
'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
If so, then you simply need to have an index column and sort the index column for decreasing values.
TTFN
RE: Copy rows macro
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
Please give an example that shows the before and after layouts because I am not understanding.
Ken
RE: Copy rows macro
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
RE: Copy rows macro
TTFN
RE: Copy rows macro
This one is before:
[URL=http://www.imageshack.us][IMG]http:
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
CODE
Rows(a & ":" & a + 1).Select
Selection.Insert Shift:=xlDown
Next
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
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
RE: Copy rows macro
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
RE: Copy rows macro
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
RE: Copy rows macro
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
Chris
Sr. Mechanical Designer, CAD
SolidWorks 05 SP2.0 / PDMWorks 05
ctopher's home site
FAQ371-376
FAQ559-1100
FAQ559-1091
FAQ559-716