0

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.

KDE
  • 115
  • 8
  • 1
    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))` – Rory Feb 23 '22 at 11:18
  • Thanks man, do you want to put that as an answer so I can accept the solution? – KDE Feb 23 '22 at 11:55

1 Answers1

2

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))
Rory
  • 32,730
  • 5
  • 32
  • 35