Why I get error "Method Range of Object _Global Failed " when refering to a range with Cells values VBA?

Question

I get the error "Method Range of Object _Global Failed " when I refer to the Range as Range(Cells(row, 1)).Select then only I get the error.

But If I refer to it as Range("A2").select then I don't get the error.

I tried giving full path as ThisWorkbook.Worksheets("FD").Range(Cells(row, 1)).Select but it didn't work either.

Below is the function I'm using,

Function copyTBdata()

Dim FD As Worksheet, CD As Worksheet
Set FD = ThisWorkbook.Worksheets("FD")


Dim row As Long

row = (ThisWorkbook.Sheets("FD").Cells(Rows.Count, 1).End(xlUp).row) + 1
   

ThisWorkbook.Worksheets("TB").Activate
Range("AD2", Range("AD2").End(xlDown).End(xlToRight)).Copy

FD.Activate
Range(Cells(row, 1)).Select
Selection.PasteSpecial Paste:=xlPasteValues
    


End Function

how to fix this issue ?

CDP1802 · Accepted Answer · 2021-12-12T14:16:41.960

In ThisWorkbook.Worksheets("FD").Range(Cells(row, 1)).Select the Cells is not qualified so will refer to the active sheet. Best to use references wherever possible.

Sub copyTBdata()

    Dim wsFD As Worksheet, wsTB As Worksheet
    Dim rowno As Long

    Set wsFD = ThisWorkbook.Worksheets("FD")
    Set wsTB = ThisWorkbook.Worksheets("TB")
    
    With wsFD
        rowno = .Cells(.Rows.Count, 1).End(xlUp).row + 1
    End With
    
    With wsTB
         .Range("AD2", .Range("AD2").End(xlDown).End(xlToRight)).Copy
         wsFD.Range("A" & rowno).PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
   
End Sub

Why I get error "Method Range of Object _Global Failed " when refering to a range with Cells values VBA?

1 Answers1