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
(E-Mail Removed)
"apaul" <(E-Mail Removed)> wrote in message
news:4EBBB33F-35C7-4D40-BB92-(E-Mail Removed)...
> 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.
>
>
Similar Threads
