Method Range of object _worksheet failed - run-time error 1004 when populating a range

oleppedersen

New Member
Joined
Mar 23, 2013
Messages
13
I am running through a bunch of workbooks, opening them, identifying one sheet in each book and want to copy the sheet to an array.

I have declared the range earlier:

Dim rT(10) as Range

...

' Run through the number of files from 1 to antT (which is declared and working)
For y = 1 To antT

' This works fine, the book is opened
Set wbT(y) = Workbooks.Open(MappeFra & filT(y) & ".xlsx")

' This works fine, the sheet is defined as no 1, which I can tell by testing with a messagebox that returns the name
Set wsT(y) = wbT(y).Sheets(1)

' Counting the number of lines in the sheet. This also works fine as the srT(y) variabile is filled the number of last row
wsT(y).Activate
With ActiveSheet
srT(y) = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

' This is just a way to see if .Range(Område) in the next command works better than .Range("A1:L" & srT(y)) (no reason it should work better, and it did not
Område = "Al:L" & Str(y)

' Here is where I get the runtime error:
Set rT(y) = wsT(y).Range(Område)
------

Normally, this would populate the range variable with the range from the worksheet, which I then transfer to the array (and can close the workbook, if I want to).

But why doesn't this work? I am probably missing something really simple here.

I have seen through quite a few run time error posts, but nothing completely similar comes up.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,215,463
Messages
6,124,963
Members
449,200
Latest member
indiansth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top