Hi all,
I'm working on this excel file. The sheet is called RawData and it has data
from column A to K. There are 3 additional sheets called Mandatory, Voluntary
and Global.
How would you write a macro to automatically look at the first row in
RawData sheet, column E, if there is a letter M, then move or copy the entire
row to the Mandatory sheet. Then goes down to the second row, same colulmn,
if there is a V, then copy or move that row to the Voluntary sheet.....then
goes down to the third row, if there is a G, then move that row to the Global
sheet...so on and so forth until it reaches the end of the RawData sheet.

Thank you so much.

What is the rule for the fourth row, and the fifth, etc.?
--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Don Doan" <Don wrote in message
...
Hi all,
I'm working on this excel file. The sheet is called RawData and it has
data
from column A to K. There are 3 additional sheets called Mandatory,
Voluntary
and Global.
How would you write a macro to automatically look at the first row in
RawData sheet, column E, if there is a letter M, then move or copy the
entire
row to the Mandatory sheet. Then goes down to the second row, same
colulmn,
if there is a V, then copy or move that row to the Voluntary
sheet.....then
goes down to the third row, if there is a G, then move that row to the
Global
sheet...so on and so forth until it reaches the end of the RawData sheet.

Thank you so much.

I'd start with one of these.

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

Don Doan wrote:

Hi all,
I'm working on this excel file. The sheet is called RawData and it has data
from column A to K. There are 3 additional sheets called Mandatory, Voluntary
and Global.
How would you write a macro to automatically look at the first row in
RawData sheet, column E, if there is a letter M, then move or copy the entire
row to the Mandatory sheet. Then goes down to the second row, same colulmn,
if there is a V, then copy or move that row to the Voluntary sheet.....then
goes down to the third row, if there is a G, then move that row to the Global
sheet...so on and so forth until it reaches the end of the RawData sheet.

Thank you so much.

--

Dave Peterson

I am pretty sure this will do what you want...

Sub MoveRows()
Dim X As Long
Dim LastRow As Long
Dim CellValue As String
Dim SheetName As String
For X = Worksheets("RawData").Cells(Rows.Count, "E"). _
End(xlUp).Row To 1 Step -1
CellValue = Worksheets("RawData").Cells(X, "E").Value
SheetName = Switch(CellValue = "M", "Mandatory", CellValue = "V", _
"Voluntary", CellValue = "G", "Global")
With Worksheets(SheetName)
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
If Len(.Cells(LastRow, 1).Value) Then LastRow = LastRow + 1
Worksheets("RawData").Cells(X, 1).EntireRow.Copy .Cells(LastRow, 1)
Worksheets("RawData").Cells(X, 1).EntireRow.Delete
End With
Next
End Sub


Rick


"Don Doan" <Don wrote in message
...
Hi all,
I'm working on this excel file. The sheet is called RawData and it has
data
from column A to K. There are 3 additional sheets called Mandatory,
Voluntary
and Global.
How would you write a macro to automatically look at the first row in
RawData sheet, column E, if there is a letter M, then move or copy the
entire
row to the Mandatory sheet. Then goes down to the second row, same
colulmn,
if there is a V, then copy or move that row to the Voluntary
sheet.....then
goes down to the third row, if there is a G, then move that row to the
Global
sheet...so on and so forth until it reaches the end of the RawData sheet.

Thank you so much.

Thanks.
How come when I typed in this line..
Worksheets("RawData").Cells(X, 1).EntireRow.Copy .Cells(LastRow, 1)
it said syntax error.

Don

"Rick Rothstein (MVP - VB)" wrote:

I am pretty sure this will do what you want...

Sub MoveRows()
Dim X As Long
Dim LastRow As Long
Dim CellValue As String
Dim SheetName As String
For X = Worksheets("RawData").Cells(Rows.Count, "E"). _
End(xlUp).Row To 1 Step -1
CellValue = Worksheets("RawData").Cells(X, "E").Value
SheetName = Switch(CellValue = "M", "Mandatory", CellValue = "V", _
"Voluntary", CellValue = "G", "Global")
With Worksheets(SheetName)
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
If Len(.Cells(LastRow, 1).Value) Then LastRow = LastRow + 1
Worksheets("RawData").Cells(X, 1).EntireRow.Copy .Cells(LastRow, 1)
Worksheets("RawData").Cells(X, 1).EntireRow.Delete
End With
Next
End Sub


Rick


"Don Doan" <Don wrote in message
...
Hi all,
I'm working on this excel file. The sheet is called RawData and it has
data
from column A to K. There are 3 additional sheets called Mandatory,
Voluntary
and Global.
How would you write a macro to automatically look at the first row in
RawData sheet, column E, if there is a letter M, then move or copy the
entire
row to the Mandatory sheet. Then goes down to the second row, same
colulmn,
if there is a V, then copy or move that row to the Voluntary
sheet.....then
goes down to the third row, if there is a G, then move that row to the
Global
sheet...so on and so forth until it reaches the end of the RawData sheet.

Thank you so much.

Out of curiosity, why are you typing it at all... just copy/paste the code I
posted into the appropriate code window and it should be fine. As for the
error... do you have the With statement (shown above it) in place? Does this
line still generate an error?

Worksheets("RawData").Cells(X, 1).EntireRow.Copy _
Worksheets(SheetName).Cells(LastRow, 1)

Rick


"Don Doan" wrote in message
...
Thanks.
How come when I typed in this line..
Worksheets("RawData").Cells(X, 1).EntireRow.Copy .Cells(LastRow, 1)
it said syntax error.

Don

"Rick Rothstein (MVP - VB)" wrote:

I am pretty sure this will do what you want...

Sub MoveRows()
Dim X As Long
Dim LastRow As Long
Dim CellValue As String
Dim SheetName As String
For X = Worksheets("RawData").Cells(Rows.Count, "E"). _
End(xlUp).Row To 1 Step -1
CellValue = Worksheets("RawData").Cells(X, "E").Value
SheetName = Switch(CellValue = "M", "Mandatory", CellValue = "V", _
"Voluntary", CellValue = "G", "Global")
With Worksheets(SheetName)
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
If Len(.Cells(LastRow, 1).Value) Then LastRow = LastRow + 1
Worksheets("RawData").Cells(X, 1).EntireRow.Copy .Cells(LastRow, 1)
Worksheets("RawData").Cells(X, 1).EntireRow.Delete
End With
Next
End Sub


Rick


"Don Doan" <Don wrote in message
...
Hi all,
I'm working on this excel file. The sheet is called RawData and it has
data
from column A to K. There are 3 additional sheets called Mandatory,
Voluntary
and Global.
How would you write a macro to automatically look at the first row in
RawData sheet, column E, if there is a letter M, then move or copy the
entire
row to the Mandatory sheet. Then goes down to the second row, same
colulmn,
if there is a V, then copy or move that row to the Voluntary
sheet.....then
goes down to the third row, if there is a G, then move that row to the
Global
sheet...so on and so forth until it reaches the end of the RawData
sheet.

Thank you so much.

It follows the same rule...row 4 column E could be a M or V or G, row 5
column E could again be a M, V or G...so on an so forth.
Basically..element in column E is the condition, if there is a M, then cut
or copy the entire row from that RawData sheet and paste it onto the
Mandatory sheet, else if there is a V, then cut or copy the whole row to the
Voluntary sheet etc...

Thanks.

"Bob Phillips" wrote:

What is the rule for the fourth row, and the fifth, etc.?
--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Don Doan" <Don wrote in message
...
Hi all,
I'm working on this excel file. The sheet is called RawData and it has
data
from column A to K. There are 3 additional sheets called Mandatory,
Voluntary
and Global.
How would you write a macro to automatically look at the first row in
RawData sheet, column E, if there is a letter M, then move or copy the
entire
row to the Mandatory sheet. Then goes down to the second row, same
colulmn,
if there is a V, then copy or move that row to the Voluntary
sheet.....then
goes down to the third row, if there is a G, then move that row to the
Global
sheet...so on and so forth until it reaches the end of the RawData sheet.

Thank you so much.

IT LOOKS GOOD NOW.
Thank you so much.
One more question....if i like to retain the data in the RawData sheet. How
would I do that??

(again, thanks a bunch...:)

"Rick Rothstein (MVP - VB)" wrote:

Out of curiosity, why are you typing it at all... just copy/paste the code I
posted into the appropriate code window and it should be fine. As for the
error... do you have the With statement (shown above it) in place? Does this
line still generate an error?

Worksheets("RawData").Cells(X, 1).EntireRow.Copy _
Worksheets(SheetName).Cells(LastRow, 1)

Rick


"Don Doan" wrote in message
...
Thanks.
How come when I typed in this line..
Worksheets("RawData").Cells(X, 1).EntireRow.Copy .Cells(LastRow, 1)
it said syntax error.

Don

"Rick Rothstein (MVP - VB)" wrote:

I am pretty sure this will do what you want...

Sub MoveRows()
Dim X As Long
Dim LastRow As Long
Dim CellValue As String
Dim SheetName As String
For X = Worksheets("RawData").Cells(Rows.Count, "E"). _
End(xlUp).Row To 1 Step -1
CellValue = Worksheets("RawData").Cells(X, "E").Value
SheetName = Switch(CellValue = "M", "Mandatory", CellValue = "V", _
"Voluntary", CellValue = "G", "Global")
With Worksheets(SheetName)
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
If Len(.Cells(LastRow, 1).Value) Then LastRow = LastRow + 1
Worksheets("RawData").Cells(X, 1).EntireRow.Copy .Cells(LastRow, 1)
Worksheets("RawData").Cells(X, 1).EntireRow.Delete
End With
Next
End Sub


Rick


"Don Doan" <Don wrote in message
...
Hi all,
I'm working on this excel file. The sheet is called RawData and it has
data
from column A to K. There are 3 additional sheets called Mandatory,
Voluntary
and Global.
How would you write a macro to automatically look at the first row in
RawData sheet, column E, if there is a letter M, then move or copy the
entire
row to the Mandatory sheet. Then goes down to the second row, same
colulmn,
if there is a V, then copy or move that row to the Voluntary
sheet.....then
goes down to the third row, if there is a G, then move that row to the
Global
sheet...so on and so forth until it reaches the end of the RawData
sheet.

Thank you so much.

IT LOOKS GOOD NOW.

Great!

Thank you so much.

You are quite welcome.

One more question....if i like to retain the data in the RawData
sheet. How would I do that??

Remove this line...

Worksheets("RawData").Cells(X, 1).EntireRow.Delete

....it is immediately above the End With statement. By the way, your original
post asked how to "then move or copy the entire row...", so I gave you the
equivalent of your first mentioned request, "move".

Rick

Just wondering....i'm interested in learning these programing stuff for
excel...what kind of course do i need to take?? i mean, the more i know
about
these excel programing, the more it would help me in my line of work....
so how did you get started in this line of work??

I'm the wrong person to be asking that question of because I'm not really an
Excel programmer. All my programming expertise (if that is really the
correct word) comes from the BASIC languages of old, a smattering of (an
old, old version of) FORTRAN, some C, some various UNIX scripting languages
and 10+ years programming with the compiled version of VB. Somewhere back in
March, I think, I looked into one of the Excel newsgroups and realized that
a large portion of what I know from the compiled version of VB can be used
(or at least adjusted to work) in Excel at both the VBA and spreadsheet
levels. Being a long-time volunteer answering questions on compiled VB
newsgroups, branching out to the Excel newsgroups seemed a reasonable thing
to do. Since I have been posting answers here, I have also been studying the
responses of the other regulars here and trying to learn what I don't know
about Excel from them. So, since I don't have an Excel-centric viewpoint of
programming (I tend to think in compiled VB and translate those concepts
into Excel's VBA or spreadsheet model), I am not the right person to advise
you of how to learn programming techniques in Excel. Hopefully, other
volunteers here will see your question and jump into this thread with their
answers. If that does not happen over the next several days (remember, we
have a weekend coming up and things slow down across them), try starting a
new thread with your question.

Rick