Still Another Copy-and-Paste Macro Problem

[Wart]

Hi, everyrone:

After confirming that the user is copying from the correct sheet, the
following code is trying to copy the entire row containing the active cell,
insert a new row at row 5 of the detination sheet, and then paste the copied
data into the new row:

Sub Copy()
ScreenUpdating = False
If ActiveSheet.Name = "Status" Then
Msg = "You must first select a row on the other sheet to copy and paste."
Style = vbOKOnly
response = MsgBox(Msg, Style)
If response = vbOK Then
GoTo 0
End If
0
Else
Worksheets("Status").Rows(5).Insert
ActiveCell.EntireRow.Copy _
Destination:=Worksheets("Status").Range("5:5")
End If
ScreenUpdating = True
End Sub

--Or that's what I want it to do, but I keep getting tripped up on the
"insert rows" part near the end. No matter where I put that line of code
(either just before or just after the line copying the active row), either I
get A) data pasted onto the current line 5 in the destination sheet,
(overwriting data that's already there), or I get nothing at all, since in
the course of inserting rows, the destination sheet becomes active and the
macro seems to be trying to copy THAT now-active row onto itself.

Either way--all I really need is for the macro to insert a new row at row 5
of the destination sheet (row 5 because I have head rows over that), and then
paste the row from the source sheet into it, pushing all of the existing data
down by one row Clearly I've gone horribly wrong. Can anyone help? Sorry to
bother everyone, but I've looked through a lot of posts about copy/paste
macros, but nothing seems quite to address my problem.

 

[Jim Cone]

First copy the row then tell Excel where to insert it.
Note: also made several other changes.
'--
Option Explicit

Sub CopyRow()
Dim Msg As String
If ActiveSheet.Name = "Status" Then
Msg = "You must first select a cell in the copy row on another sheet. "
MsgBox Msg, vbExclamation, "Alert"
Exit Sub
Else
Application.ScreenUpdating = False
ActiveCell.EntireRow.Copy
Worksheets("Status").Rows(5).Insert
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Wart" <[email protected]>
wrote in message
Hi, everyrone:
After confirming that the user is copying from the correct sheet, the
following code is trying to copy the entire row containing the active cell,
insert a new row at row 5 of the detination sheet, and then paste the copied
data into the new row:

Sub Copy()
ScreenUpdating = False
If ActiveSheet.Name = "Status" Then
Msg = "You must first select a row on the other sheet to copy and paste."
Style = vbOKOnly
response = MsgBox(Msg, Style)
If response = vbOK Then
GoTo 0
End If
0
Else
Worksheets("Status").Rows(5).Insert
ActiveCell.EntireRow.Copy _
Destination:=Worksheets("Status").Range("5:5")
End If
ScreenUpdating = True
End Sub

--Or that's what I want it to do, but I keep getting tripped up on the
"insert rows" part near the end. No matter where I put that line of code
(either just before or just after the line copying the active row), either I
get A) data pasted onto the current line 5 in the destination sheet,
(overwriting data that's already there), or I get nothing at all, since in
the course of inserting rows, the destination sheet becomes active and the
macro seems to be trying to copy THAT now-active row onto itself.

Either way--all I really need is for the macro to insert a new row at row 5
of the destination sheet (row 5 because I have head rows over that), and then
paste the row from the source sheet into it, pushing all of the existing data
down by one row Clearly I've gone horribly wrong. Can anyone help? Sorry to
bother everyone, but I've looked through a lot of posts about copy/paste
macros, but nothing seems quite to address my problem.

 

[Wart]

Oh, thank you, Jim! This is so much more elegant than that thing I was trying
to write--and it actually WORKS! Excellent! You've just made work tomorrow
about a bazillion percent easier. If I had ever figured this out (which I
wouldn't), it would have taken about, well, a bazillion years.

Thanks for all of your help! I'm sure the company I work for thanks you, too.

 

[Jim Cone]

You are welcome. The feedback is appreciated.
Jim Cone
http://www.realezsites.com/bus/primitivesoftware



"Wart"
wrote in message
Oh, thank you, Jim! This is so much more elegant than that thing I was trying
to write--and it actually WORKS! Excellent! You've just made work tomorrow
about a bazillion percent easier. If I had ever figured this out (which I
wouldn't), it would have taken about, well, a bazillion years.
Thanks for all of your help! I'm sure the company I work for thanks you, too.