I want to copy a worksheet in its entirety to a workbook in another
directory. I'm running Excel 2002 & my code (which doesn't work) is
as follows:

Option Explicit

Sub xCopyFGSht()
ActiveSheet.Select
ActiveSheet.Copy after:=Workbooks("022508.xls").Sheets(xlEnd)
ActiveSheet.Move after:=Sheets(Sheets.Count)
End Sub

The active sheet I'm copying is in R:\Batch Sheets, and I want to copy
it to
K:\Cost Accounting\Production\2008\022508.xls.

This is a network, which is why the different drives. Both workbooks
are open.

I get a run-time error '9': "Subscript out of range" error message.

I should state that if I perform this procedure manually the first
time, then try it again using the macro, it works. It's as if it
recognizes the path from one file to the other after I've done it
manually.

Hope I've given all of the facts needed. Any ideas?

Warren

How about

ActiveSheet.Copy After:=Workbooks("K:\Cost
Accounting\Production\2008\022508.xls").Sheets(1)


HTH,
JP


<(E-Mail Removed)> wrote in message
news:28e1736d-49b2-47ee-8aed-(E-Mail Removed)...
>I want to copy a worksheet in its entirety to a workbook in another
> directory. I'm running Excel 2002 & my code (which doesn't work) is
> as follows:
>
> Option Explicit
>
> Sub xCopyFGSht()
> ActiveSheet.Select
> ActiveSheet.Copy after:=Workbooks("022508.xls").Sheets(xlEnd)
> ActiveSheet.Move after:=Sheets(Sheets.Count)
> End Sub
>
> The active sheet I'm copying is in R:\Batch Sheets, and I want to copy
> it to
> K:\Cost Accounting\Production\2008\022508.xls.
>
> This is a network, which is why the different drives. Both workbooks
> are open.
>
> I get a run-time error '9': "Subscript out of range" error message.
>
> I should state that if I perform this procedure manually the first
> time, then try it again using the macro, it works. It's as if it
> recognizes the path from one file to the other after I've done it
> manually.
>
> Hope I've given all of the facts needed. Any ideas?
>
> Warren

That should have been

ActiveSheet.Copy After:= _
Workbooks("K:\Cost Accounting\Production
\2008\022508.xls").Sheets(Sheets.Count)


--JP

On Feb 27, 8:25*pm, "JP" <jp2...@earthlink.net> wrote:
> How about
>
> ActiveSheet.Copy After:=Workbooks("K:\Cost
> Accounting\Production\2008\022508.xls").Sheets(1)
>
> HTH,
> JP
>

or

ActiveSheet.Copy _
After:=Workbooks("022508.xls").Sheets(Workbooks("022508.xls").Sheets.Count)

You don't include the drive and path in the workbooks() reference, but you do
want to qualify the Sheets.count.

Or using with/end with:

with workbooks("022508.xls")
ActiveSheet.Copy _
After:=.Sheets(.Sheets.Count)
End with



JP wrote:
>
> That should have been
>
> ActiveSheet.Copy After:= _
> Workbooks("K:\Cost Accounting\Production
> \2008\022508.xls").Sheets(Sheets.Count)
>
> --JP
>
> On Feb 27, 8:25 pm, "JP" <jp2...@earthlink.net> wrote:
> > How about
> >
> > ActiveSheet.Copy After:=Workbooks("K:\Cost
> > Accounting\Production\2008\022508.xls").Sheets(1)
> >
> > HTH,
> > JP
> >

--

Dave Peterson

Good point, if both workbooks are open then you don't need the path.

Thx,
JP

On Feb 27, 8:57*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> or
>
> ActiveSheet.Copy _
> * After:=Workbooks("022508.xls").Sheets(Workbooks("022508.xls").Sheets..Count)
>
> You don't include the drive and path in the workbooks() reference, but youdo
> want to qualify the Sheets.count.
>
> Or using with/end with:
>
> with workbooks("022508.xls")
> * ActiveSheet.Copy _
> * * After:=.Sheets(.Sheets.Count)
> End with
>
>

If the file is closed, you still don't include the drive/path.

(You'd have to open the file first, though.)

JP wrote:
>
> Good point, if both workbooks are open then you don't need the path.
>
> Thx,
> JP
>
> On Feb 27, 8:57 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > or
> >
> > ActiveSheet.Copy _
> > After:=Workbooks("022508.xls").Sheets(Workbooks("022508.xls").Sheets.Count)
> >
> > You don't include the drive and path in the workbooks() reference, but you do
> > want to qualify the Sheets.count.
> >
> > Or using with/end with:
> >
> > with workbooks("022508.xls")
> > ActiveSheet.Copy _
> > After:=.Sheets(.Sheets.Count)
> > End with
> >
> >

--

Dave Peterson