Why do I get a runtime error using worksheet/sheets range method?-CodePudding

I am running into run time error 438. What is the difference between the commented line and the non commented line?

My test code is as follows:

Dim wb As Workbook
Set wb = ActiveWorkbook
Dim wsht As Worksheet
Set wsht = wb.Sheets("ABC")

g = wb.Sheets("123").Range(Cells(1, 1), Cells(12, 1))
''g = wb.wsht.Range(Cells(1, 1), Cells(12, 1))

Range(Cells(1, 2), Cells(12, 2)) = g

I am sure it's something very basic, so I apologize if it is. The non commented line works fine, where as the commented one throws out the error.

At first I thought it was because wb was already defined within wsht, but even taking out wb still throws out the error.

After some reading and the above test I thought I could use just adapt my actual code so it works with that format so I tried:

Set wb1 = ActiveWorkbook
MySheet = ActiveSheet.Name
wb1.Sheets(MySheet).Range(Cells(2, f), Cells(lastR - 1, f)) = Range(Cells(3, h), Cells(lastR, h))

Which now kicks out error 1004.

Very lost here, I even tried replacing the variable MySheet with the sheet name "Sheet8", I'm starting to think the 1004 might be due to wb1 as I cannot see why it would with the test code but not the functional code.

Any help or docs would be appreciated.

CodePudding user response：

You need to specify the sheet for the Cells properties too:

wb.Sheets("123").Range(wb.Sheets("123").Cells(1, 1), wb.Sheets("123").Cells(12, 1))