automating data copy from dynamic invoice to single spreadsheet

[apaul]

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.

 

[ker_01]

If you haven't changed the code, then the most likely culprit is a change to
the workbook itself. The code explicitly uses two worksheets; "Invoice" and
"InvData" - my guess is that one of those sheets has been renamed, and
therefore the macro can no longer find the sheet that it is looking for.

Another potential for error I see is that if you completely cleared the
InvData (destination sheet), so that you didn't even have headers anymore,
then you might have a problem when it tries to find the last used row
....).End(xlDown).Offset(1, 0)

One last thought- if your destination sheet is completely full, and there
are no more blank rows to put data into- that would also be a problem. This
may not be obvious- Excel sometimes thinks that a row is "used" even if it
looks blank to the eye... one way to tell is to look at your vertical
scrollbar. Go to the bottom of your data, and if the scrollbar is all the way
at the bottom, you are fine- but if it shows that it can keep scrolling down,
then Excel thinks you have used cells below your current location. Go to your
last row of real data, and manually select the first blank row after it.
Press Ctrl-Shift-DownArrow to select all the rows from there to the end of
the spreadsheet. Go to the menu and click 'delete' (don't use the delete key
on the keyboard). Save the file to have Excel recognize that you have cleared
/everything/ from the blank rows.

HTH,
Keith