multi cell if_copy VBA

[kris.small]

okay so i have:
Sub Copy_If_2()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim lngNextDestRow As Long<o></o>
Dim rngRowItems As Range, c As Range<o></o>
<o></o>
With Worksheets("Master input table")<o></o>
If .Range("R6:R500, Arrests").Value = True Then<o></o>
Set rngRowItems = .Range(" ")<o></o>
End If<o></o>
With Worksheets("Arrests")<o></o>
lngNextDestRow = .Cells.Find(What:="*", after:=.Range("A1"), _<o></o>
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1<o></o>
For Each c In rngRowItems<o></o>
.Cells(lngNextDestRow, c.Column) = c.Value<o></o>
'c.ClearContents 'option to "move" instead of copy<o></o>
Next c<o></o>
End With<o></o>
End With<o></o>
<o></o>
Set rngRowItems = Nothing<o></o>
Set c = Nothing<o></o>

End Sub

im not sure it it's possible to get it to do something like this.

if R6 , Arrest = true, then Copy A6,B6,C6,D6,R6,S6 to sheet "Arrests".

but i need it to do that for every Colume in R if it = Arrests

basiclly in Colum R i have 5 different avalible entries, each one needs to be moved to a different sheet, arrests, discharge, admit, called off, Transport.

QUESTION
how do i get my macro to copy A,B,C,D,E,F,G(6) if R6 = _____ (arrest in this case)

dont know if it helps by my work is old school and working with 2003 excel.

 

[VoG]

Take a look at Excel Explosion http://www.datapigtechnologies.com/freeware.htm

 

[kris.small]

Thank you, see that's why I post stuff here you guys seem to know all the fun toys in excel again thanks it should work perfectly

 

[kris.small]

So i tryed the excel explosion and it works but doesn't save the data.... kinda frustrating and it's not really user friendly which is kinda the point of this program... so seeking other option....

I have a part of the coding i need done if someone with a larger brain then me could help finish it that would be amazing...

so...

Sub Copy_If_2()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim lngNextDestRow As Long<o></o>
Dim rngRowItems As Range, c As Range<o></o>
<o></o>
With Worksheets("Master input table")<o></o>
If .Range("R6:R500").Value = "Arrest"
Set rngRowItems = .Range("A6,B6,C6,D6,E6,F6,G6,J6,k6,l6,M6,R6,S6,T6,U6,X6")<o></o>
End If<o></o>
With Worksheets("Arrest")<o></o>
lngNextDestRow = .Cells.Find(What:="*", after:=.Range("A1"), _<o></o>
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1<o></o>
For Each c In rngRowItems<o></o>
.Cells(lngNextDestRow, c.Column) = c.Value<o></o>
'c.ClearContents 'option to "move" instead of copy<o></o>
Next c<o></o>
End With<o></o>
End With<o></o>
<o></o>
Set rngRowItems = Nothing<o></o>
Set c = Nothing<o></o>
End Sub<o></o>



So that is what i have, but the problem i run into is i don't need it to just copy row 6, i need it to copy whatever row an "Arrest" in column R is found...

R151 = Arrest. Move Rown 151 to page "Arrest"
So on and so forth... if it's not an option or not really avalible to do so and aditional Add-ons anyone can think of?

thanks K

 

[VoG]

See if this helps (scroll down a bit) http://www.mrexcel.com/forum/showthread.php?t=396069