Vb/vba error

Question

Hi Miguel - I used your following code from one of your earlier interactions:

Sub CopyCells()

 Dim rngData As Range, rngDest As Long
 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

However, everytime I ran it, I kept getting an error - compile error, object required.

Am I not doing something right regarding the code? Please note that I am a newbie.

Answer

Just a couple of things. rngDest should be declared as a Range, not as Long, and the code was looking for at least two rows with headers on the InvData sheet. A fix could be something like: 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")
j = rngDest.CurrentRegion.Rows.Count

...

This will keep the functionality of appending rows, and avoid the error related to use the End property here.