Hello,

I currently have a consolidated worksheet (thanks Bill!) calle
"Dashboard" that contains closed items that are marked by a validate
column that can only contain "Closed, Open, or In-Progress." Is there
way to move the rows with a value of "Closed" to another workshee
called "Completed"? Also when this move is done, that row is no longe
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 fo
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 o
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 argen

--
nicema
-----------------------------------------------------------------------
niceman's Profile: http://www.excelforum.com/member.php...fo&userid=3478
View this thread: http://www.excelforum.com/showthread.php?threadid=54537

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


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=545376

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