copying a row to another worksheet based on a cell criteria

[Gareth 1105]

hi all

Just started to have a look at vba as I am trying to use excel more efficiently, the problem I have is this, my spreadshet consists of rows like the following

DATE NAME FACULTY REASON LESSONS
8/7 MR JONES ENGLISH ILLNESS 6
8/7 MRS MEL MATHS LOA 5
9/7 MR TOM SCIENCE MEETING 3

I want to copy the rows to other worksheets dependent on column C (faculty), so someone from english would go onto a worksheet for english,a row with maths would go into a worksheet called maths etc.

thank you in advance of any advice

 

[ravishankar]

Hi
Assuming you have all sheets named by faculty , Paste the following codes in the macro window ( Alt F11)

Sub allocate()
x = Cells(Rows.Count, 3).End(xlUp).Row
For a = 2 To x
b = Cells(a, 3)
rows(a).copy
y = Worksheets(b).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(b).rows(y+1).pastespecial
Next a
MsgBox "Completed"
End Sub

Run the macro
Ravi

 

[Gareth 1105]

thanks for the suggestion, but I get a subscript out of range error ?

 

[Gareth 1105]

apologies, I hadn't named one of the worksheets correctly, thank you very much for that

 

[Gareth 1105]

the macro seems to take the first instance of maths, but the following rows with maths in them are not recorded, this is the same for every faculty

any suggestions ?

thank you

 

[stanleydgromjr]

Gareth 1105,

I have modified ravishankar's code slightly. This should work, and not have the highlighted rows/cells after the macro.

Option Explicit
Sub allocate()
'
' Original code by ravishankar
'
' Modified 07/25/2007 by Stanley D. Grom, Jr.
'
    Dim x, a, y As Long
    Dim b As String
    Application.ScreenUpdating = False
    x = Cells(Rows.Count, 3).End(xlUp).Row
    For a = 2 To x
        b = Cells(a, 3)
        y = Worksheets(b).Cells(Rows.Count, 1).End(xlUp).Row
        Sheets("Sheet1").Range(Cells(a, "A"), Cells(a, "E")).Copy Destination:=Sheets(b).Cells(y + 1, 1)
    Next a
    Application.ScreenUpdating = True
    MsgBox "Completed"
End Sub

Have a great day,
Stan