VBA array - referring to different worksheet

[kevin chan]

I have this code which reads data into an array.
For some reason this works:

Function TotalCost(col As Integer) As Double
Dim pricedata As Variant
Dim maxrows as Integer

maxrows = Worksheets(DATASHEET).Cells(Worksheets(DATASHEET).Rows.Count, col).End(xlUp).row
pricedata = Range(Cells(1, col), Cells(maxrows, col + 5)).Value
code goes on….

But this fails on the "pricedata=" line:

Function TotalCost(col As Integer) As Double
Dim pricedata As Variant
Dim maxrows as Integer

maxrows = Worksheets(DATASHEET).Cells(Worksheets(DATASHEET).Rows.Count, col).End(xlUp).row
pricedata = Worksheets(DATASHEET).Range(Cells(1, col), Cells(maxrows, col + 5)).Value
code goes on…

The key difference is in the pricedata = line. It refers to a different worksheet
I am trying to read a contiguous block of data into an array, but I can get it to work on the same worksheet but not if it refers to another worksheet.
DATASHEET is a global constant in this case with the sheet name.
Can anyone help me with an alternative way to code this?
Thanks!

 

[MARK858]

Try either

pricedata = Worksheets(DATASHEET).Range(Worksheets(DATASHEET).Cells(1, col), Worksheets(DATASHEET).Cells(maxrows, col + 5)).Value

or

    With Worksheets(DATASHEET)
        pricedata = .Range(.Cells(1, col), .Cells(maxrows, col + 5)).Value
    End With

 

[nikio8]

Hi perhaps the first thing would be to change maxrows int to long.

Second use

Dim tws, gws As Excel.Worksheet
Set tws = ThisWorkbook.ActiveSheet
set gws = ThisWorkbook.Worksheets(DATASHEET) 'global worksheet


then it will be easier to count cells in proper worksheet any time you use cell or range. tws.cell...

 

[kevin chan]

Thank you to both of you. Implemented both your answers. Worked perfectly.