Move lines from one sheet to another

[Risky Dave]

Hi,

I have a macro that is intended to find an entry on one sheet and cut and
pastes it into the next empty line on another sheet.

I am getting the dreaded runtime 1004 with the message "Cut method of range
class failed"

The specific piece of code (part of a much larger macro) is:

If rTargetCell.Value = "" Then ' move database entry to Archive
iTgt = rTargetCell.Row
Sheets("Database").Range(iSource & ":" &
iSource).cut_ Destination:=Sheets("archive").Range(iTgt & ":" & iTgt)
Worksheets("Database").Activate
Range("a" & iSource & ":ah" &
iSource).Select
Selection.Delete Shift:=xlUp

The error is being generated at the line that starts
Sheets("Database").Range......
All the appropriate variables have been set earlier.
I think I need to specifically activate the recipient sheet, but I'm not
sure how to do this (based on similar errors elsewhere in these forums).

Any suggestions would be gratefully received.

This is in Office 2003 under Windows 2000, if that makes a difference

TIA

Dave

 

[Nick H]

Hi Dave,

This line looks odd to me (even after correcting the line-wrap)...

Sheets("Database").Range(iSource & ":" & iSource).cut_
Destination:=Sheets("archive").Range(iTgt & ":" & iTgt)

....I don't think the Underscore character belongs there. It would
normally be used like this to break a long line of code into two or
more to make the code more readable...

Sheets("Database").Range(iSource & ":" & iSource).cut _
Destination:=Sheets("archive").Range(iTgt & ":" & iTgt)

....try that and see if it helps. Also you shouldn't need to select
your range to delete it e.g...

Range("a" & iSource & ":ah" & iSource).Delete Shift:=xlUp

....should be just as effective (watch out for line-wrap - this is a
single line).

Br, Nick

 

[Don Guillett]

try
If rTargetCell.Value = "" Then
iTgt = rTargetCell.Row
Sheets("Database").Range(iSource & ":" & iSource).cut _
Destination:=Sheets("archive").Range(iTgt & ":" & iTgt)
end if
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.