I am trying to get the code below to check through column Q of a
worksheet, when it finds a value that is in the case statement I want
to copy the entire row over to another worksheet.

So I'm trying to do this

Code:
--------------------
in worksheet "manning" check each cell in column Q
if cell = "actual"
then copy row to worksheet "actual"
if cell = "substantive"
then copy row to worksheet "substantive"
--------------------


At the moment it is not copying any data across.




Code:
--------------------
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Ws3 As Worksheet

Dim Ws1Q As Range

Dim ActualVacRow As Long
Dim SubstantiveVacRow As Long

Dim Cell1 As Range

Set Ws1 = Workbooks("manning.xls").Sheets("manning")
Set Ws2 = Workbooks("manning.xls").Sheets("Actual")
Set Ws3 = Workbooks("manning.xls").Sheets("Substantive")

Set Ws1Q = Ws1.Columns("Q")

For Each Cell1 In Ws1.Range("Q1:q" & Range("q65536").End(xlUp).Row)
Select Case UCase(C)
Case "ACTUAL": ActualVacRow = Ws2.Range("a65536").End(xlUp).Row + 1
Ws1.Rows(Cell1.Row).Copy Destination:=Ws2.Rows(ActualVacRow)
Case "SUBSTANTIVE": SubstantiveVacRow = Ws3.Range("a65536").End(xlUp).Row + 1
Ws1.Rows(Cell1.Row).Copy Destination:=Ws3.Rows(SubstantiveVacRow)
End Select
Next Cell1
--------------------



---
Message posted from http://www.ExcelForum.com/