if an element in a row is true then copy whole row to another shee

[Don Doan]

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.

 

[Bob Phillips]

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)

 

[Dave Peterson]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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\)]

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]

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.

 

[Don Doan]

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\)]

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

 

[Rick Rothstein \(MVP - VB\)]

When I originally worked up the code for your question, I mistakenly thought
Column A had the M/V/G codes. After I noticed you actually said Column E, I
corrected my code. However, I missed changing 2 lines which could mean the
code won't work correctly if your row of data has nothing in Column A. Since
we know Column E must have something in it (either an M, V or G) and since
that column is copied into the other sheets, then using Column E would
guarantee the code would always work. Hence, you should use this corrected
subroutine instead...

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, "E").End(xlUp).Row
If Len(.Cells(LastRow, "E").Value) Then LastRow = LastRow + 1
Worksheets("RawData").Cells(X, 1).EntireRow.Copy .Cells(LastRow, 1)
End With
Next
End Sub

Note: I removed the line that was deleting the data from the RawData sheet.

By the way, you do realize, if you leave the original data on the RawData
sheet, you can't just add new data to it, right? If you were to simply add
new data to the old data, then when you run my code again, the old data
would be recopied into the other sheets again (along with the new data).

Rick

 

[Don Doan]

hi,
That's okie....most likely i won't add any new data into the sheet after i
ran the macro.
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??

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Don Doan]

hi Rick,
just one more small problem.
When i run the program...it said run-time error '94', invalide use of null.
When i clicked on the debug button, it highlight the line
Sheetname = Switch(CellValue = "M".....

any reason why??

Don

 

[Rick Rothstein \(MVP - VB\)]

Not sure what to tell you (you did copy/paste the code into your project,
right?)... the code worked fine on my copy of Excel 2003 (I tested it before
posting). What version are you using? Another (remote) possibility... go to
Tools/References on the VB Editor's menubar... are any of the references
marked as "Missing"? If so, put a check mark next to them. For comparison,
my checked off References are these:

Visual Basic For Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library

Rick