1
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".

Zoe
  • 27,060
  • 21
  • 118
  • 148
srt
  • 521
  • 3
  • 11
  • 22

3 Answers3

0

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
Santosh
  • 12,175
  • 4
  • 41
  • 72
0

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
PatricK
  • 6,375
  • 1
  • 21
  • 25
0

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

JMP
  • 4,417
  • 17
  • 30
  • 41