Amit,
I think you are going to have your best luck by moving "End If"
from above the Debug statement
to just before "Next i"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Amit Shanker" wrote in message
Hello wise folks,
With WinXP Pro SP2 and Office 2003 SP1, I have written following code
that works (watch out for line wraps):

Option Explicit
Option Base 1

Sub Copy_Values_To_Billing_Sheet()

Dim firstCell As Range
Dim lastCell As Range
Dim i As Long
Dim myCounter As Long
Dim myCopyArray As Variant

myCounter = 3
i = 0

Application.ScreenUpdating = False

With Sheets("Client")
Set firstCell = Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
Set lastCell = Range("C3")
For i = firstCell.Row To lastCell.Row Step -1
If Cells(i, firstCell.Column).Interior.ColorIndex = 15 Then
myCounter = myCounter + 2
With Cells(i, firstCell.Column)
myCopyArray = Array(.Offset(-1, 0), .Offset(-1, -1), .Offset(0, 4), .Offset(0, 6))
End With
End If
Debug.Print myCopyArray(1), myCopyArray(2), myCopyArray(3),myCopyArray4)
Sheets("monthly billing").Cells(5, myCounter) = myCopyArray(1)
Sheets("monthly billing").Cells(6, myCounter) = myCopyArray(2)
Sheets("monthly billing").Cells(14, myCounter + 1) = myCopyArray(3)
Sheets("monthly billing").Cells(27, myCounter) = myCopyArray(4)
Next i
End With

Set firstCell = Nothing
Set lastCell = Nothing
Set myCopyArray = Nothing
Application.ScreenUpdating = True

End Sub

Thanks,
Amit