Insert and Copy Data to new Workbook Row
 

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Insert and Copy Data to new Workbook Row
 

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

"Ozzie via OfficeKB.com" <u18021@uwe a écrit dans le message de
news:5f5b6d83af8d8@uwe...
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Insert and Copy Data to new Workbook Row
 

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


Ardus Petus wrote:
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.

--
Message posted via http://www.officekb.com

Insert and Copy Data to new Workbook Row
 

Maybe the other thread's reply will work.

"Ozzie via OfficeKB.com" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

--

Dave Peterson

Insert and Copy Data to new Workbook Row
 

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

Dave Peterson wrote:
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.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Insert and Copy Data to new Workbook Row
 

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




Dave Peterson wrote:
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.


--
Message posted via http://www.officekb.com

Insert and Copy Data to new Workbook Row
 

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.

"Ozzie via OfficeKB.com" wrote:

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

Dave Peterson wrote:
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.


--
Message posted via http://www.officekb.com

--

Dave Peterson

Insert and Copy Data to new Workbook Row
 

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")



Dave Peterson wrote:
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.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

Insert and Copy Data to new Workbook Row
 

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")


"Ozzie via OfficeKB.com" wrote:

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")

Dave Peterson wrote:
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.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1

--

Dave Peterson

Insert and Copy Data to new Workbook Row
 

Dave, cheers for that and many thanks for your help

Dave Peterson wrote:
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]

Can anyone assist?, i am using 2003.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1