Hi OssieMac,

Yes, I'd like to move all the data and formatting but leave the blank row in
the source worksheet, but since they are going to look empty in between
others row, it would be nice to insert a line of code that will sort them in
certain order so, that the user don't have to manually sort the data.

I'm using Excel 2004 for Mac, but I do know how to attach the the code to
the button, so that's not a problem for me.

I got "Run-time error ;91': Object variable or with block variable not set"
for the following code:

cel.EntireRow.Cut Destination:= _
.Cells(.Rows.Count, "A") _
.End(xlUp).Offset(1, 0)

Please allow me to explain myself in detail of what I need:
Currently, in my excel workbook, I have 3 sheets: 1. WaitList (aka source
worksheet) 2. Enrolled 3. Rejected.
WaiList sheet has Column A-O. Column K is the Status column (Enrolled,
Rejected, Waiting). I have set source worksheet to have 550 rows, and I'd
like to keep it in that amount even after move the data to other sheets.

Here is my idea of the code should look like:

Search the entire [WaitList] sheet

If Col K in [WaitList] is "Enrolled"
Then move the data from Col A to Col O to [Enrolled]

Else if Col K in [WaitList] is "Rejected"
Then move the data from Col A to Col O to [Rejected]

(append the data for [Enrolled and [Rejected] to the next row everyone time
the code is run)

Sort the [WaitList] by Col H in acsending order and then Col G in acsending
order.

I hope this help you in the processing of trying to comprehend my problem
better. I'm sorry that I change some of the term and column from the
original post.

Thank you so much,

Neon520



"OssieMac" wrote:

Hi Neon,

I took a gamble that your answer to my previous post will be yes and if so,
the following code should do what you want. If you need help in attaching the
code to a button then please get back to me but let me know what version of
xl you are using so I can tailor the instructions to suit.

Sub MoveDeletes()
Dim wsSht1 As Worksheet
Dim wsMove As Worksheet
Dim rngColA As Range
Dim cel As Range

Set wsSht1 = Sheets("Sheet1")
Set wsMove = Sheets("Move")

With wsSht1
Set rngColA = .Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

With wsMove
For Each cel In rngColA
If cel.Value = "Delete" Then
cel.EntireRow.Cut Destination:= _
.Cells(.Rows.Count, "A") _
.End(xlUp).Offset(1, 0)

cel.EntireRow.ClearFormats
End If
Next cel
End With

End Sub

--
Regards,

OssieMac