Prev: SaveAs in Excel97-2003 format
Next: Sumproduct error
From: Risky Dave on 26 Mar 2010 06:06
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
From: Nick H on 26 Mar 2010 07:04
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
From: Don Guillett on 26 Mar 2010 08:25
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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Risky Dave" <RiskyDave(a)discussions.microsoft.com> wrote in message
news:B6B8728D-DC6B-455B-8192-2DF7E01C6B5E(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: SaveAs in Excel97-2003 format
Next: Sumproduct error