Copy Rows To Sheet Based On Cell Value In Range

[JoeMicro]

HI:
Could anybody help me

i need a macro button on my excel sheet that should do the following.

i have a range (Ex: A3:E46) which is being continuously filled, i need to transfer the information to another sheet depending on column "E", (Ex: if "E3" = "A" paste row to sheet2 if = "B" paste row to sheet3) and so on each row could have another string in column "E".

after the paste is done it should clear the range making place for new entries, that should also be copied finding the next empty cell (it shouldn't delete the old entries)

i have got this code already, but it doesn't copy the entire Row, it just copy's information in column "E".

Sub VCopy()
    Dim strWs As String
    Dim strPass As String
    Dim rCell As Range

 For Each rCell In Range("E3",Cells(Rows.Count,"E").End(xlUp))
         
        Select  Case UCase(rCell)
        Case "A": strWs = "Sheet1"
        Case "B": strWs = "Sheet2"
        Case "C": strWs = "Sheet3"
             'Follow the pattern' End Select
         
        rCell.EntireRow.Copy _
        Destination:= Sheets(strWs).Cells(Rows.Count, 1).End(xlUp)(2, 1)
         
    Next rCell
    Range("E3", Cells(Rows.Count, "E").End(xlUp)).Clear
End Sub

Thanks in Advanced

 

[SkipVought]

Hmmmmm???

I run your code and it copies the entire row.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[JoeMicro]

Thanks
I realized what my problem was, the

End Select

was unrecognized

Thanks a lot

 

[JoeMicro]

I need a bit more help with this code,

what it does now is, it copy's the entire row, but I need to copy only until "F" not further, because now it

overwrites my formulas on the other sheets.

Thanks

 

[StephenJR]

Replace

rCell.EntireRow.Copy

with

rCell.Resize(,2).Copy

 

[SkipVought]

        range(Cells(rCell.row,1),Cells(rCell.row,"F")) .Copy _
        Destination:= Sheets(strWs).Cells(Rows.Count, 1).End(xlUp)(2, 1)

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[StephenJR]

Don't mind me, I think Skip's is right. I was thinking you were only copying columns E and F but I re-read the question and it seems you're copying A to E. Apologies.

 

[JoeMicro]

Thank you so much "Skip" it's a great Help.

and thanks "StephenJR" for your time, and trying.