Method 'Range' of object '_Global' failed Error

Question

1.Dim destbook As Workbook
2.Dim destsheet As Worksheet
3.Set destbook = ActiveWorkbook
4.Set destsheet = destbook.Sheets(1)
5.Dim ct As Integer
6.destsheet.Range("C1048576").Select
7.ct = Selection.End(xlUp).Row
8.Windows("Book1.xlsm").Activate
9.Sheets("Sheet1").Activate
10.Range(Cells(ct, 1)).Offset(1, 0).Select

Here on 10th line i am getting a error saying "Method 'Range' of object '_Global' failed".

Answer 1

Try below code. Avoid using Activate , Select, Activeworkbook in your code for better results.

Sub test()

    Dim destbook As Workbook
    Dim destsheet As Worksheet

    Set destbook = ThisWorkbook
    Set destsheet = destbook.Sheets(1)

    Dim ct As Integer
    With destsheet
        ct = .Range("C" & .Rows.Count).End(xlUp).Row
    End With

    Windows("Book1.xlsm").Activate
    With Sheets("Sheet1")
        .Select
        .Range(.Cells(ct, 1)).Offset(1, 0).Select
    End With

End Sub

Answer 2

It you just want to select the cell below the last cell of column C in first worksheet:

Sub SO_18909094()
    Dim destbook As Workbook
    Dim destsheet As Worksheet

    Set destbook = ActiveWorkbook
    Set destsheet = destbook.Sheets(1)

    destsheet.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select

    Set destbook = Nothing
    Set destsheet = Nothing
End Sub

Answer 3

Cells(ct,1) refers to the value of the cell at coordinates (ct,1), and is not actually a valid cell address (unless of course you make it so!), and so Range throws an exception.

You can just drop the Range and it works.

Cells(ct, 1).Offset(1, 0).Select