Insert and Copy Data to new Workbook Row

[Ozzie via OfficeKB.com]

Apologies for the re-post but I originally posted this request into the wrong
forum!

I need to insert a new row into a workbook where a character in Column "A" =
"D" and then copy the row which is two rows above into the new inserted row.

I can insert the row ok with the following code;

Sub Add_Rows()

Dim lastrow As Long, i As Long

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1
If Cells(i, 1) = "D" Then
Rows(i).Insert
End If
Next
End Sub

but what I can't do is copy the cells in 2 rows above this newly inserted row?

..

I have been searching and reading numerous threads and links but to no avail.

Can anyone assist?, i am using 2003.

 

[Ardus Petus]

Sub Add_Rows()

Dim lastrow As Long, i As Long

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1
If Cells(i, 1) = "D" Then
Rows(i).Insert
Rows(i - 2).Copy Rows(i)
End If
Next
End Sub

HTH

 

[Ozzie via OfficeKB.com]

Hi Ardus,

Cheers for your speedy response, unfortunately though the code isn't working
properly.

I think the problem is that the code appears to copy from bottom up and not
top down as is needed, apologies for not being clearer.

Regards

David

Sub Add_Rows()

Dim lastrow As Long, i As Long

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1
If Cells(i, 1) = "D" Then
Rows(i).Insert
Rows(i - 2).Copy Rows(i)
End If
Next
End Sub

HTH
--
AP

Apologies for the re-post but I originally posted this request into the wrong
forum!

[quoted text clipped - 23 lines]

Can anyone assist?, i am using 2003.

 

[Dave Peterson]

Maybe the other thread's reply will work.

Apologies for the re-post but I originally posted this request into the wrong
forum!

I need to insert a new row into a workbook where a character in Column "A" =
"D" and then copy the row which is two rows above into the new inserted row.

I can insert the row ok with the following code;

Sub Add_Rows()

Dim lastrow As Long, i As Long

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1
If Cells(i, 1) = "D" Then
Rows(i).Insert
End If
Next
End Sub

but what I can't do is copy the cells in 2 rows above this newly inserted row?

.

I have been searching and reading numerous threads and links but to no avail.

Can anyone assist?, i am using 2003.

 

[Ozzie via OfficeKB.com]

Hi Dave,

Just tried your code but like the previous post, both sets of code work from
bottom up? and I need top down.

Cheers

David

Maybe the other thread's reply will work.

Apologies for the re-post but I originally posted this request into the wrong
forum!

[quoted text clipped - 23 lines]

Can anyone assist?, i am using 2003.

 

[Ozzie via OfficeKB.com]

Dave is the problem my original piece of code which is making the copying of
the rows work from bottom up?;

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1

Maybe the other thread's reply will work.

Apologies for the re-post but I originally posted this request into the wrong
forum!

[quoted text clipped - 23 lines]

Can anyone assist?, i am using 2003.

 

[Dave Peterson]

It's usually lots easier to work from the bottom up.

Then you don't have to worry about what row you're on.

But the code I suggested worked fine for me. It copied the row two rows above
under the current row.

I'm not sure what you want.

Dave is the problem my original piece of code which is making the copying of
the rows work from bottom up?;

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 2 Step -1

Maybe the other thread's reply will work.

Apologies for the re-post but I originally posted this request into the wrong
forum!

[quoted text clipped - 23 lines]

Can anyone assist?, i am using 2003.

 

[Ozzie via OfficeKB.com]

Morning Dave,

I have nearly got the code that I need, with help from Toppers, but there is
1 final bit though that hopefully you might know. My code is;

Sub InsertLines()

With Worksheets("Claim Upload")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

For r = lastrow To 1 Step -1

If .Cells(r, "A") = "D" Then

.Cells(r, "A").Offset(1, 0).EntireRow.Insert
.Cells(r, "A").Offset(1, 0).Resize(1, 5) = Array("X", "DSINV", "C1",
"01", "cap")
.Cells(r, "A").Offset(1, 5) = Left(.Cells(r, "F"), 9)
.Cells(r, "A").Offset(1, 6) = .Cells(r, "c")
.Cells(r, "A").Offset(1, 9) = 1
.Cells(r, "A").Offset(1, 10).Resize(1, 2) = .Cells(r, "D")
.Cells(r, "A").Offset(1, 12).Resize(1, 2) = Array("no", "no")

End If
Next r

End With

End Sub

however, i need the "01" on the line below to be text and not a number format,
but am stuck on how to do it??

..Cells(r, "A").Offset(1, 0).Resize(1, 5) = Array("X", "DSINV", "C1", "01",
"cap")

It's usually lots easier to work from the bottom up.

Then you don't have to worry about what row you're on.

But the code I suggested worked fine for me. It copied the row two rows above
under the current row.

I'm not sure what you want.

Dave is the problem my original piece of code which is making the copying of
the rows work from bottom up?;

[quoted text clipped - 9 lines]

 

[Dave Peterson]

You can format all 5 cells.

with .Cells(r, "A").Offset(1, 0).Resize(1, 5)
.numberformat = "@"
.value = Array("X", "DSINV", "C1", "01", "cap")
end with

or just format that one cell.

.cells(r,"A").offset(1,3).numberformat = "@"
.Cells(r, "A").Offset(1, 0).Resize(1, 5) _
= Array("X", "DSINV", "C1", "01", "cap")

Morning Dave,

I have nearly got the code that I need, with help from Toppers, but there is
1 final bit though that hopefully you might know. My code is;

Sub InsertLines()

With Worksheets("Claim Upload")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

For r = lastrow To 1 Step -1

If .Cells(r, "A") = "D" Then

.Cells(r, "A").Offset(1, 0).EntireRow.Insert
.Cells(r, "A").Offset(1, 0).Resize(1, 5) = Array("X", "DSINV", "C1",
"01", "cap")
.Cells(r, "A").Offset(1, 5) = Left(.Cells(r, "F"), 9)
.Cells(r, "A").Offset(1, 6) = .Cells(r, "c")
.Cells(r, "A").Offset(1, 9) = 1
.Cells(r, "A").Offset(1, 10).Resize(1, 2) = .Cells(r, "D")
.Cells(r, "A").Offset(1, 12).Resize(1, 2) = Array("no", "no")

End If
Next r

End With

End Sub

however, i need the "01" on the line below to be text and not a number format,
but am stuck on how to do it??

.Cells(r, "A").Offset(1, 0).Resize(1, 5) = Array("X", "DSINV", "C1", "01",
"cap")

It's usually lots easier to work from the bottom up.

Then you don't have to worry about what row you're on.

But the code I suggested worked fine for me. It copied the row two rows above
under the current row.

I'm not sure what you want.

Dave is the problem my original piece of code which is making the copying of
the rows work from bottom up?;

[quoted text clipped - 9 lines]

Can anyone assist?, i am using 2003.

 

[Ozzie via OfficeKB.com]

Dave, cheers for that and many thanks for your help

You can format all 5 cells.

with .Cells(r, "A").Offset(1, 0).Resize(1, 5)
.numberformat = "@"
.value = Array("X", "DSINV", "C1", "01", "cap")
end with

or just format that one cell.

.cells(r,"A").offset(1,3).numberformat = "@"
.Cells(r, "A").Offset(1, 0).Resize(1, 5) _
= Array("X", "DSINV", "C1", "01", "cap")

Morning Dave,

[quoted text clipped - 46 lines]