Sub dd() 'Look at the bottom row and dim as long for the row.

Dim rngData As Range,
rngDest As Long
Dim i as integer ' may need double or long?
dim j As Integer ' may need double or long?
rngDest = Sheets("InvData").Cells(Rows.Count, 1).End(xlUp).Row + 1
End Sub

Also, you may be able to copy blocks and delete blanks instead of one cell
at a time.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"apaul" wrote in message
...
Hi - I am a newbie vis-a-vis VBA. I am trying to copy data off of an
invoice
(that keeps changing every day) to a single spreadsheet. So far, I have
used
the following code:
Sub CopyCells()
Dim rngData As Range, rngDest As Range
Dim i, j As Integer

Set rngData = Sheets("Invoice").Range("A16:g35")
Set rngDest = Sheets("InvData").Range("A1").End(xlDown).Offset(1 , 0)

For i = 1 To rngData.Rows.Count
If rngData.Cells(i, 1) < "" Then
rngDest.Offset(j, 0).Value = Sheets("Invoice").Range("b8").Value
rngDest.Offset(j, 1).Value = Sheets("Invoice").Range("b10").Value
rngDest.Offset(j, 2).Value = rngData.Cells(i, 1).Value
rngDest.Offset(j, 3).Value = rngData.Cells(i, 2).Value
rngDest.Offset(j, 4).Value = rngData.Cells(i, 3).Value
rngDest.Offset(j, 5).Value = rngData.Cells(i, 4).Value
rngDest.Offset(j, 6).Value = rngData.Cells(i, 6).Value
rngDest.Offset(j, 7).Value = Sheets("Invoice").Range("b9").Text
j = j + 1
End If
Next
End Sub

Now every time I run it, I keep getting the following error:
Run-time error '1004'
Application-defined or object-defined error.

Any help would be highly appreciated.