runtime error 1004 : excel VBA

Question

I keep getting runtime error 1004, on the following line:

originBook.Sheets(1).Range(Cells(1, 1), Cells(lastRow, lastCol)).Copy

Here's the full code

Sub Obtain_Source()

    Application.DisplayAlerts = False

    Dim theOrigin, theString, newCol As String
    Dim lastRow, lastCol As Long
    Dim theRange As Range
    Dim originBook, originBookBackup, macroBook As Workbook
    Dim originOpen As Boolean

    originOpen = False

    Set macroBook = Workbooks("FY_Macro_Testt (DYNAMIC).xlsm")

    theOrigin = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls; *.xlsm; *.xlsx), *.xls' *.xlsm' *.xlsx", _
    Title:="Fiscal Year Selection: Select Only One", ButtonText:="Open", MultiSelect:=False)

    If TypeName(theOrigin) = "Boolean" Then
        MsgBox "Don't just stand there.  Do something." & vbNewLine & _
               "Quit hitting CANCEL.  >.< ", vbExclamation, "WARNING.  CHOKING HAZARD."
    Else
        originOpen = True
        Set originBook = Workbooks.Open(theOrigin)
            lastRow = Range("A65536").End(xlUp).Row
            lastCol = Range("XFD1").End(xlToLeft).Column
            lastCol = originBook.Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

        originBook.Sheets(2).Visible = False
        originBook.Sheets(3).Visible = False

        originBook.Sheets(1).Range(Cells(1, 1), Cells(lastRow, lastCol)).Copy
        macroBook.Sheets(1).Cells(6, 1).PasteSpecial
        i = 1000
       Do While 1000 <= 20000
           j = i - 999
            If originBook.Sheets(1).Cells(i, 1).Value <> vbNullString Or _
               originBook.Sheets(1).Cells(i, 1).Value <> "" Then
                originBook.Sheets(1).Range(Cells(j, 1), Cells(i - 1, lastCol)).Copy
               macroBook.Sheets(1).Cells(j + 5, 1).PasteSpecial

            End If
           i = i + 1000
        Loop
       originBook.Sheets(2).Visible = True
       originBook.Sheets(3).Visible = True
    End If

    If originOpen = True Then
        originBook.Close
    End If

End Sub

which one should I change?

Answer 1

Your error will almost certainly be because you are using

originBook.Sheets(1).Range(Cells(1, 1), Cells(lastRow, lastCol)).Copy

instead of, as @ShaiRado pointed out,

originBook.Sheets(1).Range(originBook.Sheets(1).Cells(1, 1), _
                           originBook.Sheets(1).Cells(lastRow, lastCol)).Copy

When they are not fully qualified, Cells references refer to cells on the ActiveSheet. Excel is therefore having to try to copy all the cells on Sheets(1) that lie in the area between two cells on the ActiveSheet. It's equivalent to saying "choose all the houses in Los Angeles that lie in the area between the intersection of E 79th St and 1st Avenue and the intersection of E 86th St and York Ave New York". (Not living in the USA, I hope that analogy makes sense.)