Copying Sheet - subscript out of range error

I tried to use this command
Sheets("Report").Copy Before:=Workbooks(xl_file).Sheets(1)
where xl_file is an object containing the full path to the file I want to
copy the sheet into.

When I ran this I got 'data type mismatch' so I defined xl_file_fmt as a
string which made this statement;

Sheets("Report").Copy Before:=Workbooks(xl_file_fmt).Sheets(1)

Now I'm getting a 'subscrit out of range' error.....

ARGHH.... don't know if I'll ever get this stuff.

I'm sure someone out there can tell me the problem that I'm obviously
overlooking

tia
Steve

workbooks(xxxx)
expects just the name--no drive, no path.

like:

dim xl_file as string
xl_file = "book1.xls"
Sheets("Report").Copy Before:=Workbooks(xl_file).Sheets(1)

And that receiving file has to be open.

"GSP@DCS" wrote:
>
> I tried to use this command
> Sheets("Report").Copy Before:=Workbooks(xl_file).Sheets(1)
> where xl_file is an object containing the full path to the file I want to
> copy the sheet into.
>
> When I ran this I got 'data type mismatch' so I defined xl_file_fmt as a
> string which made this statement;
>
> Sheets("Report").Copy Before:=Workbooks(xl_file_fmt).Sheets(1)
>
> Now I'm getting a 'subscrit out of range' error.....
>
> ARGHH.... don't know if I'll ever get this stuff.
>
> I'm sure someone out there can tell me the problem that I'm obviously
> overlooking
>
> tia
> Steve

--

Dave Peterson