VBA: Error "1004" When trying to pull values from a worksheet

Question

When trying to place values from a worksheet range into a variant array, I need to have the worksheet selected despite having placed the worksheet name in my code.

Sub SetCoords()

Dim CoordsVariant As Variant

ReDim CoordsVariant(1 To 2, 1 To 3)

CoordsVariant = ThisWorkbook.Worksheets("Geometry and Properties").Range(Cells(4, 3), Cells(5, 5)).Value

End Sub

If I have the worksheet "Geometry and Properties" selected in the background then it works fine. If not I get an error despite telling it the name of the worksheet to go to.

Answer 1

The issue is that you qualified the Range but not the Cells, so it is looking at the active sheet. If the active sheet is not the sheet to which the range is referring you will get the error.

Use a With Block and requisite . to denote proper parentage of ALL the range objects.

Sub SetCoords()

Dim CoordsVariant As Variant
ReDim CoordsVariant(1 To 2, 1 To 3)

With ThisWorkbook.Worksheets("Geometry and Properties")
    CoordsVariant = .Range(.Cells(4, 3), .Cells(4 + NNodes - 1, 5)).Value
End With

End Sub

Answer 2

Your problem is that you are using ThisWorkbook. ThisWorkbook is whatever workbook is active at the moment. I'm betting that you have more than one workbooks open.

When you have another workbook active, your code is trying to look for that worksheet in the active workbook which doesn't have a worksheet with that name.

Use the following code instead:

Sub SetCoords()

Dim CoordsVariant As Variant

ReDim CoordsVariant(1 To 2, 1 To 3)

CoordsVariant = Application.Workbooks("{WRITE OUT THE NAME OF YOUR WORKBOOK HERE}").Worksheets("Geometry and Properties").Range(Cells(4, 3), Cells(4 + NNodes - 1, 5)).Value

End Sub

Answer 3

You are using the Range, you need to add the following line before:

ThisWorkbook.Worksheets("Geometry and Properties").Activate