Generating 1004 error messages while running a marco

Hello,

I currently have a consolidated worksheet (thanks Bill!) called "Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed.

Edit: here are some additional information:

The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.)

The "status" column is at column 11.

i have a snippet of code which does the above purpose

Sub MoveClosed()
Sheets("Dashboard").Select
Selection.AutoFilter Field:=1, Criteria1:="Closed"
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select
' Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.Offset(0, 13).Range("A1")).Select
Selection.Copy
Sheets("Completed").Select
Range("A7").Select
' The next 3 steps require that there be at least two rows of data in Completed
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Dashboard").Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1
ActiveCell.Select
End Sub

but when i am run this marco .it genrates an error message saying

Run-time error '1004':

Application-defined or object-define error

it is showing error
ActiveCell.offset(1,0).Range("A1").Select

Help Me please its argent

Try this

sub MoveClosed()
sheets("Completed").cells.delete ' delete this if you want to keep existing info
sheets("Dashboard").Select
[1:6].copy sheets("Completed").[a1] ' and this...
for each c in range([k7], cells(activesheet.usedrange.rows.count,11))
if c.value = "Closed" then c.entirerow.copy sheets("Completed").[a1].end(xldown).offset(1,0)
next
end sub


I reckon your problem was that when you did "selection.end(xldown).select" and then tried to offset to the next row you were already at the bottom row so there wasn't another cell to offset to.
Col

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"niceman" wrote:

>
> Hello,
>
> I currently have a consolidated worksheet (thanks Bill!) called
> "Dashboard" that contains closed items that are marked by a validated
> column that can only contain "Closed, Open, or In-Progress." Is there a
> way to move the rows with a value of "Closed" to another worksheet
> called "Completed"? Also when this move is done, that row is no longer
> necessary in Dashboard and should be removed.
>
> Edit: here are some additional information:
>
> The worksheet has a locked header that is 6 rows deep (the values for
> the "Status" column begins on row 7 and on.)
>
> The "status" column is at column 11.
>
> i have a snippet of code which does the above purpose
>
> Sub MoveClosed()
> Sheets("Dashboard").Select
> Selection.AutoFilter Field:=1, Criteria1:="Closed"
> Range("A7").Select
> Range(Selection, Selection.End(xlDown)).Select
> ' Range(Selection, Selection.End(xlToRight)).Select
> Range(Selection, ActiveCell.Offset(0, 13).Range("A1")).Select
> Selection.Copy
> Sheets("Completed").Select
> Range("A7").Select
> ' The next 3 steps require that there be at least two rows of
> data in Completed
> Selection.End(xlDown).Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> ActiveSheet.Paste
> Sheets("Dashboard").Select
> Application.CutCopyMode = False
> Selection.EntireRow.Delete
> Selection.AutoFilter Field:=1
> ActiveCell.Select
> End Sub
>
> but when i am run this marco .it genrates an error message saying
>
> Run-time error '1004':
>
> Application-defined or object-define error
>
> it is showing error
> ActiveCell.offset(1,0).Range("A1").Select
>
> Help Me please its argent
>
>
> --
> niceman
> ------------------------------------------------------------------------
> niceman's Profile: http://www.excelforum.com/member.php...o&userid=34781
> View this thread: http://www.excelforum.com/showthread...hreadid=545376
>
>