Hi Everyone,

I'm sure this is a piece of cake for most of you who are familiar with
programming in Excel, but it's a bit challenge for me.

I'd like to know how to program in Excel so that rows that have been flagged
with some identifier will be move to a different sheet, for example:
In Sheet1, Col A is the flag column. It will be a drop down list. Let's
say if Sheet1 Col A says "DELETE" then that entire row show be move to the
sheet called "MOVE". Since Sheet1 is to be certain amount of row, I don't
want to Cut the entire row to the sheet "MOVE." I'd like to move the data and
the formatting only and leave the amount of row in Sheet1 the same after
moving.

I'd like to link the code with a button, just so that the user can click
when he/she is ready to do so.

Thank you so much for any advice/help!

Neon520

Hi Neon

Try this for size. Uses auto filter to move data. You might want to
consider clearing the range of the destination sheet or putting the
new data at the bottom of that range. You will have to change the
amount of columns in the code to suit your needs as I used 6.

Take care

Marcus

Option Explicit
Sub MoveData()

Dim Rng As Range
Dim lw As Integer

Application.ScreenUpdating = False
Set Rng = Range([A1], Range("A" & Rows.Count).End(xlUp))

With Rng ' use autofilter
.AutoFilter , Field:=1, Criteria1:="Delete" 'Criteria DELETE
Range("A2").Select
lw = Range("A" & Rows.Count).End(xlUp).Row
Range(Cells(2, 1), Cells(lw, 6)).Copy Worksheets
("Sheet2").Range("A2")
.AutoFilter
End With

End Sub

By the following statement do you mean that you want to move all the data and
formatting but leave the blank row in the source worksheet?

"I'd like to move the data and the formatting only and leave the amount of
row in Sheet1 the same after moving."

--
Regards,

OssieMac


"Neon520" wrote:

Hi Everyone,

I'm sure this is a piece of cake for most of you who are familiar with
programming in Excel, but it's a bit challenge for me.

I'd like to know how to program in Excel so that rows that have been flagged
with some identifier will be move to a different sheet, for example:
In Sheet1, Col A is the flag column. It will be a drop down list. Let's
say if Sheet1 Col A says "DELETE" then that entire row show be move to the
sheet called "MOVE". Since Sheet1 is to be certain amount of row, I don't
want to Cut the entire row to the sheet "MOVE." I'd like to move the data and
the formatting only and leave the amount of row in Sheet1 the same after
moving.

I'd like to link the code with a button, just so that the user can click
when he/she is ready to do so.

Thank you so much for any advice/help!

Neon520

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

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

I tested with a PC and it appears to work. I don't have access to a Macintosh
so can't test under those conditions. However, did you edit your worksheet
names correctly in the following code.

Set wsSht1 = Sheets("Sheet1") 'this is the source sheet
Set wsMove = Sheets("Move") 'this is the destination sheet

Other than that, post the code with a question under the Mactintosh area and
someone might be able to help with whatever needs to be tweeked for Macintosh.

--
Regards,

OssieMac

Hi OssieMac,

I retry the code again with some small changes and this time it works!
However, there is a little tweak that I'd like to see if you can help me with:

I delete the code line: cel.EntireRow.ClearFormats because I do want to
preserve the formatting that is originally with Sheet1; however, even with
this code line deleted, I do still lose the border line that is originally in
Sheet1. Is there a way to preserve the border setting as well?

Also, could you include a couple more lines or code that will sort the
Sheet1 by ColA then ColB by ascending order after Transferring the info to
Move sheet?

Optional request (if possible, if not, that's fine)
Is it possible to show a textbox pop up saying that "there is no 'Delete'
Item in Sheet1 and press OK to quit" something like that? Just so the user
will know that...?

Thank you for your great help!!
Neon520

"OssieMac" wrote:

I tested with a PC and it appears to work. I don't have access to a Macintosh
so can't test under those conditions. However, did you edit your worksheet
names correctly in the following code.

Set wsSht1 = Sheets("Sheet1") 'this is the source sheet
Set wsMove = Sheets("Move") 'this is the destination sheet

Other than that, post the code with a question under the Mactintosh area and
someone might be able to help with whatever needs to be tweeked for Macintosh.

--
Regards,

OssieMac