Copy worksheet to a file in another directory

[warrent]

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

 

[JP]

How about

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


HTH,
JP

 

[JP]

That should have been

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


--JP

 

[Dave Peterson]

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]

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

Thx,
JP

 

[Dave Peterson]

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

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