This macro is attempting to perform the following:
If a value in D Col is in the array then the line is copied to the
associated worksheet.
However if the value isnt in the array but the G Col value is "CC", then
line should
be copied to "WP" worksheet,
If neither condition holds but H Col value is XY then copies line to "Other"
Worksheet

Its with the two else statements that Im having problems, my last error
message was 'Object Required'

Many Thanks



Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
'If rDest.Row < 18 Then _
' Set rDest = rDest.Offset(18 - rDest.Row, 0)
.EntireRow.Copy Destination:=rDest
Else: If .Offset(0, 3).Value = "CC" Then
EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
Else: If .Offset(0, 4).Value = "XY" Then EntireRow.Copy _
Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)

Exit For
End If
Next i
End With
Next rCell
End Sub

Teresa,

I hesitate to say that this worked for me as I don't have your data, but it
didn't reproduce the error.

What does the data look like?

Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=rDest
ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
ElseIf .Offset(0, 4).Value = "XY" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)
Exit For
End If
Next i
End With
Next rCell
End Sub

--
HTH

Bob Phillips

"teresa" wrote in message
...
This macro is attempting to perform the following:
If a value in D Col is in the array then the line is copied to the
associated worksheet.
However if the value isnt in the array but the G Col value is "CC", then
line should
be copied to "WP" worksheet,
If neither condition holds but H Col value is XY then copies line to
"Other"
Worksheet

Its with the two else statements that Im having problems, my last error
message was 'Object Required'

Many Thanks



Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
'If rDest.Row < 18 Then _
' Set rDest = rDest.Offset(18 - rDest.Row, 0)
.EntireRow.Copy Destination:=rDest
Else: If .Offset(0, 3).Value = "CC" Then
EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
Else: If .Offset(0, 4).Value = "XY" Then EntireRow.Copy _
Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)

Exit For
End If
Next i
End With
Next rCell
End Sub

Hi Bob, Ive just run through the code and at the line below (when Criteria is
met) I get Run-time error '424' Object Required.

ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)


What does the data look like?

Normal spreadsheet, cols A:M
thre are two blank columns between D and G

Any more questions, let me know- thanks

ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _

I get

"Bob Phillips" wrote:

Teresa,

I hesitate to say that this worked for me as I don't have your data, but it
didn't reproduce the error.

What does the data look like?

Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=rDest
ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
ElseIf .Offset(0, 4).Value = "XY" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)
Exit For
End If
Next i
End With
Next rCell
End Sub

--
HTH

Bob Phillips

"teresa" wrote in message
...
This macro is attempting to perform the following:
If a value in D Col is in the array then the line is copied to the
associated worksheet.
However if the value isnt in the array but the G Col value is "CC", then
line should
be copied to "WP" worksheet,
If neither condition holds but H Col value is XY then copies line to
"Other"
Worksheet

Its with the two else statements that Im having problems, my last error
message was 'Object Required'

Many Thanks



Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
'If rDest.Row < 18 Then _
' Set rDest = rDest.Offset(18 - rDest.Row, 0)
.EntireRow.Copy Destination:=rDest
Else: If .Offset(0, 3).Value = "CC" Then
EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
Else: If .Offset(0, 4).Value = "XY" Then EntireRow.Copy _
Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)

Exit For
End If
Next i
End With
Next rCell
End Sub

what is in the columns though? It looks to me that you are trying to address
off-sheet, but I can't replicate yet.

--
HTH

Bob Phillips

"teresa" wrote in message
...
Hi Bob, Ive just run through the code and at the line below (when Criteria
is
met) I get Run-time error '424' Object Required.

ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)


What does the data look like?

Normal spreadsheet, cols A:M
thre are two blank columns between D and G

Any more questions, let me know- thanks

ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _

I get

"Bob Phillips" wrote:

Teresa,

I hesitate to say that this worked for me as I don't have your data, but
it
didn't reproduce the error.

What does the data look like?

Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=rDest
ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
ElseIf .Offset(0, 4).Value = "XY" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)
Exit For
End If
Next i
End With
Next rCell
End Sub

--
HTH

Bob Phillips

"teresa" wrote in message
...
This macro is attempting to perform the following:
If a value in D Col is in the array then the line is copied to the
associated worksheet.
However if the value isnt in the array but the G Col value is "CC",
then
line should
be copied to "WP" worksheet,
If neither condition holds but H Col value is XY then copies line to
"Other"
Worksheet

Its with the two else statements that Im having problems, my last
error
message was 'Object Required'

Many Thanks



Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
'If rDest.Row < 18 Then _
' Set rDest = rDest.Offset(18 -
rDest.Row, 0)
.EntireRow.Copy Destination:=rDest
Else: If .Offset(0, 3).Value = "CC" Then
EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
Else: If .Offset(0, 4).Value = "XY" Then EntireRow.Copy _
Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)

Exit For
End If
Next i
End With
Next rCell
End Sub

A B C D E F
G
Job Number Project Title Value Client Name Executive
1 asd 10000 ACCENT CC

There are 50 rows like above format
not all the G values are Populated,

Dont know if this is relevant but all the cells are formatted "General"

"Bob Phillips" wrote:

what is in the columns though? It looks to me that you are trying to address
off-sheet, but I can't replicate yet.

--
HTH

Bob Phillips

"teresa" wrote in message
...
Hi Bob, Ive just run through the code and at the line below (when Criteria
is
met) I get Run-time error '424' Object Required.

ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)


What does the data look like?

Normal spreadsheet, cols A:M
thre are two blank columns between D and G

Any more questions, let me know- thanks

ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _

I get

"Bob Phillips" wrote:

Teresa,

I hesitate to say that this worked for me as I don't have your data, but
it
didn't reproduce the error.

What does the data look like?

Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=rDest
ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
ElseIf .Offset(0, 4).Value = "XY" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)
Exit For
End If
Next i
End With
Next rCell
End Sub

--
HTH

Bob Phillips

"teresa" wrote in message
...
This macro is attempting to perform the following:
If a value in D Col is in the array then the line is copied to the
associated worksheet.
However if the value isnt in the array but the G Col value is "CC",
then
line should
be copied to "WP" worksheet,
If neither condition holds but H Col value is XY then copies line to
"Other"
Worksheet

Its with the two else statements that Im having problems, my last
error
message was 'Object Required'

Many Thanks



Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
'If rDest.Row < 18 Then _
' Set rDest = rDest.Offset(18 -
rDest.Row, 0)
.EntireRow.Copy Destination:=rDest
Else: If .Offset(0, 3).Value = "CC" Then
EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
Else: If .Offset(0, 4).Value = "XY" Then EntireRow.Copy _
Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)

Exit For
End If
Next i
End With
Next rCell
End Sub

Teresa,

Can you post a spreadsheet somewhere, somewhere like
http://flypicture.com/index.cfm?display=upload

--
HTH

Bob Phillips

"teresa" wrote in message
...

A B C D E F
G
Job Number Project Title Value Client Name Executive
1 asd 10000 ACCENT CC

There are 50 rows like above format
not all the G values are Populated,

Dont know if this is relevant but all the cells are formatted "General"

"Bob Phillips" wrote:

what is in the columns though? It looks to me that you are trying to
address
off-sheet, but I can't replicate yet.

--
HTH

Bob Phillips

"teresa" wrote in message
...
Hi Bob, Ive just run through the code and at the line below (when
Criteria
is
met) I get Run-time error '424' Object Required.

ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)


What does the data look like?

Normal spreadsheet, cols A:M
thre are two blank columns between D and G

Any more questions, let me know- thanks

ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _

I get

"Bob Phillips" wrote:

Teresa,

I hesitate to say that this worked for me as I don't have your data,
but
it
didn't reproduce the error.

What does the data look like?

Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=rDest
ElseIf .Offset(0, 3).Value = "CC" Then
.EntireRow.Copy _
Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
ElseIf .Offset(0, 4).Value = "XY" Then
.EntireRow.Copy _

Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)
Exit For
End If
Next i
End With
Next rCell
End Sub

--
HTH

Bob Phillips

"teresa" wrote in message
...
This macro is attempting to perform the following:
If a value in D Col is in the array then the line is copied to the
associated worksheet.
However if the value isnt in the array but the G Col value is
"CC",
then
line should
be copied to "WP" worksheet,
If neither condition holds but H Col value is XY then copies line
to
"Other"
Worksheet

Its with the two else statements that Im having problems, my last
error
message was 'Object Required'

Many Thanks



Public Sub coiD()
Dim fin As Workbook
Dim vArr As Variant
Dim rCell As Range
Dim rDest As Range
Dim i As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans.xls")
vArr = Array("Hudson", "HS", "C&W")
For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
If .Value = vArr(i) Then
Set rDest =
fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
'If rDest.Row < 18 Then _
' Set rDest = rDest.Offset(18 -
rDest.Row, 0)
.EntireRow.Copy Destination:=rDest
Else: If .Offset(0, 3).Value = "CC" Then
EntireRow.Copy _

Destination:=fin.Worksheets("WP").Cells(25,
1).End(xlUp).Offset(1, 0)
Else: If .Offset(0, 4).Value = "XY" Then EntireRow.Copy _

Destination:=fin.Worksheets("Other").Cells(25,
1).End(xlUp).Offset(1, 0)

Exit For
End If
Next i
End With
Next rCell
End Sub