Why am I getting error unwrapping Optional if it has a value?

Vb/vba error

• 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.