automating data copy from dynamic invoice to single spreadsheet
 

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.

automating data copy from dynamic invoice to single spreadsheet
 

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.