Copy worksheet to a file in another directory
 

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

Copy worksheet to a file in another directory
 

How about

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


HTH,
JP


wrote in message
...
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

Copy worksheet to a file in another directory
 

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" wrote:
How about

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

HTH,
JP

Copy worksheet to a file in another directory
 

or

ActiveSheet.Copy _
After:=Workbooks("022508.xls").Sheets(Workbooks("0 22508.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" wrote:
How about

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

HTH,
JP


--

Dave Peterson

Copy worksheet to a file in another directory
 

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

Thx,
JP

On Feb 27, 8:57*pm, Dave Peterson wrote:
or

ActiveSheet.Copy _
* After:=Workbooks("022508.xls").Sheets(Workbooks("0 22508.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

Copy worksheet to a file in another directory
 

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 wrote:
or

ActiveSheet.Copy _
After:=Workbooks("022508.xls").Sheets(Workbooks("0 22508.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