Error 1004 with Range(Cells(...

OK, this is probably a simple problem, but I can't figure it out. I set a range variable (WeeklyDates) to a single row of "week ending" dates at the top of a table (cells D1:BU1). I want to copy a 6-month block of "week ending" dates, ending with a user-specified date (Input_EndDate, which is a named range in ThisWorkbook). All the dates are Friday dates. Macro is running in ThisWorkbook (which is the active workbook); the WeeklyDates range is in another (open) file. Here is some of my code:

Please Login or Register  to view this content.

WeeklyDates gets correctly set as D1:BU1 on the sheet containing the data. EndCol is correctly assigned as 69 (Input_EndDate matches the value in BT1 of the data sheet). StartCol is correctly set to 44. But I get an Error 1004 when I try to execute the Range.Copy command. What gives? WeeklyDates is a valid range object (one row by 70 columns). Cells(1,44) and (1,69) are within that range. So why is it bombing out? What am I missing here?

Why doesn't VBA like my original code? Doesn't it fit the Range(Cells(row1,col1),Cells(row2,col2)) syntax, since WeeklyDates is declared as a range?
And if the snytax is wrong, why didn't it throw an error at compile time?

OK, now I'm REALLY confused.
WeeklyDates is a range object, correct? I had originally tried:
WeeklyDates.Range(Cells(1, StartCol), Cells(1, EndCol)).Copy
(which looks to me like it should be using the range property of the WeeklyDates range object, right?), but that ALSO gives an Error 1004.
And now you're saying that WeeklyDates(44,69) (in my example) would work? Where is that described in the VBA documentation? I can't find anything that describes a Range syntax like that. I thought you had to use the Cells property (when using row and column numbers, rather than an A1-style reference).

OK, that makes a little more sense! Thanks!
Is there a shorthand method for referencing an area (more than one row and column) within a range object that denotes a large table? For example, if MyTable is a range object (variable), how would I denote the range of Cells(2,1) to Cells(5,5) within MyTable? I guess the long way would be:

Please Login or Register  to view this content.

Is there a shorthand substitute?