Need Simple Copy and Insert Row, Move Down Macro
 

Hello,

Is there simple macro to copy and insert existing row data and then move
down and repeat for all rows?

Example needed:

Rows existing
1
2
3
4

Rows after Macro
1
1
2
2
3
3
4
4

Thanks

Need Simple Copy and Insert Row, Move Down Macro
 

I just used the Macro Recorder and got this, this macro starts at A1
and copies to A6:A7
then goes to A2 and copies to A8:A9
and so on, this will work if you only have a few lines to copy, if you
have many lines then we can do a do until code, if you require that
then let us know




Sub CopyDblLines()
'
' CopyDblLines Macro
' Macro recorded 2/23/2006 by Dave
'

'
Range("A1").Select
Selection.Copy
ActiveCell.Offset(5, 0).Range("A1:A2").Select
ActiveSheet.Paste
ActiveCell.Offset(-4, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(6, 0).Range("A1:A2").Select
ActiveSheet.Paste
ActiveCell.Offset(-5, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(7, 0).Range("A1:A2").Select
ActiveSheet.Paste
ActiveCell.Offset(-6, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(8, 0).Range("A1:A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(-7, 0).Range("A1").Select
End Sub

Need Simple Copy and Insert Row, Move Down Macro
 

Sub AddRows()
Dim lasrow As Long, i As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastRow To 1 Step -1
Rows(i + 1).Insert
Cells(i + 1, 1).Value = Cells(i, 1).Value
Next
End Sub


worked for me with your numbers in column 1 starting in A1

--
Regards,
Tom Ogilvy


wrote in message
...
Hello,

Is there simple macro to copy and insert existing row data and then move
down and repeat for all rows?

Example needed:

Rows existing
1
2
3
4

Rows after Macro
1
1
2
2
3
3
4
4

Thanks

Need Simple Copy and Insert Row, Move Down Macro
 

that's great,
this code is not option explict, is it??

Need Simple Copy and Insert Row, Move Down Macro
 

Thanks for the help, I tried to figure it by looking at recorded code as
well but couldn't figure past doing it all manually.

Your code does as shown but I need to get a diff result. I need to copy
the existing row of data intact directly below where I started.
Basically inserting a copied row and then move down and repeat next
single number

I tried yours out and it wipes out the rows in between.

my ends up being
1 1
2 2
3 3
4 4
5 5
6 1
7 1
8 2
9 2
10 3
3

I need to end up with exactly
1
1
2
2

from the very first line with each pair being duplicate exact

If you have time to pass some more, thanks, I'll play with your code
myself and use it to learn the results of the syntax line by line,
always figured out more that way than trying to lookup in VBA help.

Need Simple Copy and Insert Row, Move Down Macro
 

oh I see
1
new row 1
2
new row 2

Need Simple Copy and Insert Row, Move Down Macro
 

It was meant to be, but not tested using option explict. There is a typo in
an declaration, but not in the executable statements.

Sub AddRows()
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
Rows(i + 1).Insert
Cells(i + 1, 1).Value = Cells(i, 1).Value
Next
End Sub


--
Regards,
Tom Ogilvy

"dmexcel" wrote in message
oups.com...
that's great,
this code is not option explict, is it??

Need Simple Copy and Insert Row, Move Down Macro
 

dmexcel wrote:
that's great,
this code is not option explict, is it??

Sorry, I don't know what that term even means. All the Excel I've ever
learned has been trial and error and help files.

From both yourself and Tom Ogilvy, the code appears to work on the
single column, Tom's got me exactly the 1 1 2 2 3 3 sequence, but it
isn't carrying the rest of the row data with it.

Sorry If I needed to specify, the rows have several columns of filled
data in them.

Tom's ended up giving me
1 A A A A
1
2 B B B B
2
3 C C C C
3

It doesn't seem to copy the entire row data to produce
1 A A A A
1 A A A A
2 B B B B
2 B B B B
etc

Need Simple Copy and Insert Row, Move Down Macro
 

Sub CopyNewRow()
'
' CopyNewRow Macro
' Macro recorded 2/23/2006 by Dave
'

'
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-7, 1).Range("A1").Select
Application.CutCopyMode = False
End Sub

Need Simple Copy and Insert Row, Move Down Macro
 

Yes, you should say what you mean. You said rows existing and then showed a
single column of numbers. Here is an adjustment.

Sub AddRows()
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
Rows(i + 1).Insert
Rows(i + 1).FillDown
Next
End Sub

--
Regards,
Tom Ogilvy


wrote in message
...
dmexcel wrote:
that's great,
this code is not option explict, is it??

Sorry, I don't know what that term even means. All the Excel I've ever
learned has been trial and error and help files.

From both yourself and Tom Ogilvy, the code appears to work on the
single column, Tom's got me exactly the 1 1 2 2 3 3 sequence, but it
isn't carrying the rest of the row data with it.

Sorry If I needed to specify, the rows have several columns of filled
data in them.

Tom's ended up giving me
1 A A A A
1
2 B B B B
2
3 C C C C
3

It doesn't seem to copy the entire row data to produce
1 A A A A
1 A A A A
2 B B B B
2 B B B B
etc

Need Simple Copy and Insert Row, Move Down Macro
 

Works for me

Need Simple Copy and Insert Row, Move Down Macro
 

Tom Ogilvy wrote:
Yes, you should say what you mean. You said rows existing and then showed a
single column of numbers. Here is an adjustment.

Sub AddRows()
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
Rows(i + 1).Insert
Rows(i + 1).FillDown
Next
End Sub


Sorry, Apologize for that,

Yes that last does the trick. I did try tweaking it, wondering if just
changing the

Cells(i + 1, 1).Value = Cells(i, 1).Value
to
Row(i + 1, 1).Value = Row(i, 1).Value

was the answer, but I never would have got it to continue without your
code knowledge. I usually will try to trial and error it till I learn
it but sooner or later I guess I need to get some real VBA training.

Thanks much for all the help, gonna save me hours of work tomorrow

Cheers

Need Simple Copy and Insert Row, Move Down Macro
 

your change would have been

Row(i + 1).Value = Row(i).Value

That would work if you know before hand that all the values are constants
and not formulas or you want to change any formulas to constants.
Unfortunately, I don't know that. So I used FillDown
--
Regards,
Tom Ogilvy

wrote in message
...
Tom Ogilvy wrote:
Yes, you should say what you mean. You said rows existing and then
showed a
single column of numbers. Here is an adjustment.

Sub AddRows()
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
Rows(i + 1).Insert
Rows(i + 1).FillDown
Next
End Sub


Sorry, Apologize for that,

Yes that last does the trick. I did try tweaking it, wondering if just
changing the

Cells(i + 1, 1).Value = Cells(i, 1).Value
to
Row(i + 1, 1).Value = Row(i, 1).Value

was the answer, but I never would have got it to continue without your
code knowledge. I usually will try to trial and error it till I learn
it but sooner or later I guess I need to get some real VBA training.

Thanks much for all the help, gonna save me hours of work tomorrow

Cheers