Getting Range of Object - Worksheet Error in fully referenced code

[TheRedCardinal]

In my code, I set a variable WS1 as follows:

Set WBk1 = ThisWorkbook
Set WS1 = WBk1.Sheets("2. Final Data")

I then import a sheet from another workbook as follows:

s = "<Full Path>"
t = Dir(s)

    If t = "" Then

        MsgBox ("The VN Product File could not be found - please select it to open")
        With Application.Dialogs(xlDialogOpen)
            If .Show = True Then
                Set WBk2 = ActiveWorkbook
            End If
        End With

        Else
    
            Workbooks.Open Filename:=s
            Set WBk2 = ActiveWorkbook
    
    End If
        
WBk2.Sheets(1).Copy Before:=WBk1.Sheets("Data Sheet")
WBk2.Close SaveChanges:=False
ActiveSheet.Name = "RP"

Then finally I attempt to load a column from WS1 into an array:

Set CellA = WS1.Range("A1:Z1").Find("Item number")

WS1.Columns(CellA.Column + 1).Insert Shift:=xlToLeft
WS1.Cells(1, CellA.Column + 1) = "Origin"

'Collate Item Numbers into a Single Table

Set CellA = WS1.Range("A1:Z1").Find("Item")
LCol = WS1.Range("Final_Table[#Data]").Rows.Count

TableItems = WS1.Range(Cells(2, CellA.Column), Cells(LCol, CellA.Column))

TableItems has been declared as a Variant

But I get the Range of Object error in the final line where I allocate the column to Table Items.

I have hovered over the variables:

CellA.Column = 9
LCol = 80
WS1 = Worksheet/Sheet 3 - which is correct

What silly error have I made with this?

Thanks!

 

[RoryA]

You didn't qualify the Cells properties with the sheet:

TableItems = WS1.Range(WS1.Cells(2, CellA.Column), WS1.Cells(LCol, CellA.Column))

 

[TheRedCardinal]

So much for fully referenced

Thanks!

 

[RoryA]

Glad to help.

By the way, instead of this:

            Workbooks.Open Filename:=s
            Set WBk2 = ActiveWorkbook

you should really do this:

            Set WBk2 = Workbooks.Open(Filename:=s)

 

[TheRedCardinal]

Ah thanks!
I tried dabbling with than and got an error, so I went back to what I knew.

Think it's because I did this:

Set WBk2 = Workbooks.Open Filename:=s

Regardless, noted with thanks again!