Hi,
Am getting an error that says 'copy method of worksheet class failed". My
code is
Windows("2006-01-OAKAM-MOP.xls").Activate
Worksheets("OAKAM Facility Diagram").Activate
Sheets("OAKAM Facility Diagram").Copy Befo=Workbooks( _
"2006-02-OAKAM-MOP.xls").Worksheets("AM Flight Schedule")

Is there a workaround or am I doing something wrong? I am just copying a
worksheet from workbook A to workbook B.

Thanks,
Chet
--
Chet Shannon

All you need is:

Workbooks("2006-01-OAKAM-MOP.xls") _
.Worksheets("OAKAM Facility Diagram").Copy _
Befo=Workbooks("2006-02-OAKAM-MOP.xls") _
.Worksheets("AM Flight Schedule")

--
Regards,
Tom Ogilvy

"Chet Shannon" wrote in message
...
Hi,
Am getting an error that says 'copy method of worksheet class failed". My
code is
Windows("2006-01-OAKAM-MOP.xls").Activate
Worksheets("OAKAM Facility Diagram").Activate
Sheets("OAKAM Facility Diagram").Copy Befo=Workbooks( _
"2006-02-OAKAM-MOP.xls").Worksheets("AM Flight Schedule")

Is there a workaround or am I doing something wrong? I am just copying a
worksheet from workbook A to workbook B.

Thanks,
Chet
--
Chet Shannon

Hi Tom,
I tried the code as you mentioned and I still got the same error message.

Thanks,
Chet
--
Chet Shannon


"Tom Ogilvy" wrote:

All you need is:

Workbooks("2006-01-OAKAM-MOP.xls") _
.Worksheets("OAKAM Facility Diagram").Copy _
Befo=Workbooks("2006-02-OAKAM-MOP.xls") _
.Worksheets("AM Flight Schedule")

--
Regards,
Tom Ogilvy

"Chet Shannon" wrote in message
...
Hi,
Am getting an error that says 'copy method of worksheet class failed". My
code is
Windows("2006-01-OAKAM-MOP.xls").Activate
Worksheets("OAKAM Facility Diagram").Activate
Sheets("OAKAM Facility Diagram").Copy Befo=Workbooks( _
"2006-02-OAKAM-MOP.xls").Worksheets("AM Flight Schedule")

Is there a workaround or am I doing something wrong? I am just copying a
worksheet from workbook A to workbook B.

Thanks,
Chet
--
Chet Shannon

In contrast, I opened two workbooks and gave them and their worksheets
corresponding names to those you posted and the code ran flawlessly for me.

--
Regards,
Tom Ogilvy


"Chet Shannon" wrote in message
...
Hi Tom,
I tried the code as you mentioned and I still got the same error message.

Thanks,
Chet
--
Chet Shannon


"Tom Ogilvy" wrote:

All you need is:

Workbooks("2006-01-OAKAM-MOP.xls") _
.Worksheets("OAKAM Facility Diagram").Copy _
Befo=Workbooks("2006-02-OAKAM-MOP.xls") _
.Worksheets("AM Flight Schedule")

--
Regards,
Tom Ogilvy

"Chet Shannon" wrote in message
...
Hi,
Am getting an error that says 'copy method of worksheet class failed".
My
code is
Windows("2006-01-OAKAM-MOP.xls").Activate
Worksheets("OAKAM Facility Diagram").Activate
Sheets("OAKAM Facility Diagram").Copy Befo=Workbooks( _
"2006-02-OAKAM-MOP.xls").Worksheets("AM Flight Schedule")

Is there a workaround or am I doing something wrong? I am just
copying a
worksheet from workbook A to workbook B.

Thanks,
Chet
--
Chet Shannon

OK.. might be related to my workbook itself then... Thx Chet
--
Chet Shannon


"Tom Ogilvy" wrote:

In contrast, I opened two workbooks and gave them and their worksheets
corresponding names to those you posted and the code ran flawlessly for me.

--
Regards,
Tom Ogilvy


"Chet Shannon" wrote in message
...
Hi Tom,
I tried the code as you mentioned and I still got the same error message.

Thanks,
Chet
--
Chet Shannon


"Tom Ogilvy" wrote:

All you need is:

Workbooks("2006-01-OAKAM-MOP.xls") _
.Worksheets("OAKAM Facility Diagram").Copy _
Befo=Workbooks("2006-02-OAKAM-MOP.xls") _
.Worksheets("AM Flight Schedule")

--
Regards,
Tom Ogilvy

"Chet Shannon" wrote in message
...
Hi,
Am getting an error that says 'copy method of worksheet class failed".
My
code is
Windows("2006-01-OAKAM-MOP.xls").Activate
Worksheets("OAKAM Facility Diagram").Activate
Sheets("OAKAM Facility Diagram").Copy Befo=Workbooks( _
"2006-02-OAKAM-MOP.xls").Worksheets("AM Flight Schedule")

Is there a workaround or am I doing something wrong? I am just
copying a
worksheet from workbook A to workbook B.

Thanks,
Chet
--
Chet Shannon

Yeah I took the code an copied to a new workbook and it worked fine. There
is something about the target workbook causing this problem it would seem. I
have no idea what could be doing it though.
--
Chet Shannon


"Chet Shannon" wrote:

OK.. might be related to my workbook itself then... Thx Chet
--
Chet Shannon


"Tom Ogilvy" wrote:

In contrast, I opened two workbooks and gave them and their worksheets
corresponding names to those you posted and the code ran flawlessly for me.

--
Regards,
Tom Ogilvy


"Chet Shannon" wrote in message
...
Hi Tom,
I tried the code as you mentioned and I still got the same error message.

Thanks,
Chet
--
Chet Shannon


"Tom Ogilvy" wrote:

All you need is:

Workbooks("2006-01-OAKAM-MOP.xls") _
.Worksheets("OAKAM Facility Diagram").Copy _
Befo=Workbooks("2006-02-OAKAM-MOP.xls") _
.Worksheets("AM Flight Schedule")

--
Regards,
Tom Ogilvy

"Chet Shannon" wrote in message
...
Hi,
Am getting an error that says 'copy method of worksheet class failed".
My
code is
Windows("2006-01-OAKAM-MOP.xls").Activate
Worksheets("OAKAM Facility Diagram").Activate
Sheets("OAKAM Facility Diagram").Copy Befo=Workbooks( _
"2006-02-OAKAM-MOP.xls").Worksheets("AM Flight Schedule")

Is there a workaround or am I doing something wrong? I am just
copying a
worksheet from workbook A to workbook B.

Thanks,
Chet
--
Chet Shannon

I have found a knowledge base article referring to this. It is Copying
worksheet programmatically causes run-time error 1004 in Excel and is article
number 210684. Microsoft acknowledges this is happening and offers a
work-around of (see pasted in solution)
RESOLUTION
Microsoft provides programming examples for illustration only, without
warranty either expressed or implied. This includes, but is not limited to,
the implied warranties of merchantability or fitness for a particular
purpose. This article assumes that you are familiar with the programming
language that is being demonstrated and with the tools that are used to
create and to debug procedures. Microsoft support engineers can help explain
the functionality of a particular procedure, but they will not modify these
examples to provide added functionality or construct procedures to meet your
specific requirements. To resolve this problem, save and close the workbook
periodically while the copy process is occurring, as in the following sample
code: Sub CopySheetTest()
Dim iTemp As Integer
Dim oBook As Workbook
Dim iCounter As Integer

' Create a new blank workbook:
iTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set oBook = Application.Workbooks.Add
Application.SheetsInNewWorkbook = iTemp

' Add a defined name to the workbook
' that RefersTo a range:
oBook.Names.Add Name:="tempRange", _
RefersTo:="=Sheet1!$A$1"

' Save the workbook:
oBook.SaveAs "c:\test2.xls"

' Copy the sheet in a loop. Eventually,
' you get error 1004: Copy Method of
' Worksheet class failed.
For iCounter = 1 To 275
oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
'Uncomment this code for the workaround:
'Save, close, and reopen after every 100 iterations:
If iCounter Mod 100 = 0 Then
oBook.Close SaveChanges:=True
Set oBook = Nothing
Set oBook = Application.Workbooks.Open("c:\test2.xls")
End If
Next
End Sub


--
Chet Shannon


"Chet Shannon" wrote:

Hi,
Am getting an error that says 'copy method of worksheet class failed". My
code is
Windows("2006-01-OAKAM-MOP.xls").Activate
Worksheets("OAKAM Facility Diagram").Activate
Sheets("OAKAM Facility Diagram").Copy Befo=Workbooks( _
"2006-02-OAKAM-MOP.xls").Worksheets("AM Flight Schedule")

Is there a workaround or am I doing something wrong? I am just copying a
worksheet from workbook A to workbook B.

Thanks,
Chet
--
Chet Shannon

You copying hundreds of sheets? I thought it was only one and it is to a
different workbook unlike the situation in the article.

--
Regards,
Tom Ogilvy



"Chet Shannon" wrote in message
...
I have found a knowledge base article referring to this. It is Copying
worksheet programmatically causes run-time error 1004 in Excel and is
article
number 210684. Microsoft acknowledges this is happening and offers a
work-around of (see pasted in solution)
RESOLUTION
Microsoft provides programming examples for illustration only, without
warranty either expressed or implied. This includes, but is not limited
to,
the implied warranties of merchantability or fitness for a particular
purpose. This article assumes that you are familiar with the programming
language that is being demonstrated and with the tools that are used to
create and to debug procedures. Microsoft support engineers can help
explain
the functionality of a particular procedure, but they will not modify
these
examples to provide added functionality or construct procedures to meet
your
specific requirements. To resolve this problem, save and close the
workbook
periodically while the copy process is occurring, as in the following
sample
code: Sub CopySheetTest()
Dim iTemp As Integer
Dim oBook As Workbook
Dim iCounter As Integer

' Create a new blank workbook:
iTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set oBook = Application.Workbooks.Add
Application.SheetsInNewWorkbook = iTemp

' Add a defined name to the workbook
' that RefersTo a range:
oBook.Names.Add Name:="tempRange", _
RefersTo:="=Sheet1!$A$1"

' Save the workbook:
oBook.SaveAs "c:\test2.xls"

' Copy the sheet in a loop. Eventually,
' you get error 1004: Copy Method of
' Worksheet class failed.
For iCounter = 1 To 275
oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
'Uncomment this code for the workaround:
'Save, close, and reopen after every 100 iterations:
If iCounter Mod 100 = 0 Then
oBook.Close SaveChanges:=True
Set oBook = Nothing
Set oBook = Application.Workbooks.Open("c:\test2.xls")
End If
Next
End Sub


--
Chet Shannon


"Chet Shannon" wrote:

Hi,
Am getting an error that says 'copy method of worksheet class failed".
My
code is
Windows("2006-01-OAKAM-MOP.xls").Activate
Worksheets("OAKAM Facility Diagram").Activate
Sheets("OAKAM Facility Diagram").Copy Befo=Workbooks( _
"2006-02-OAKAM-MOP.xls").Worksheets("AM Flight Schedule")

Is there a workaround or am I doing something wrong? I am just copying
a
worksheet from workbook A to workbook B.

Thanks,
Chet
--
Chet Shannon

This actually also happens after about 36 sheets and even when copying
between files.

"Tom Ogilvy" wrote:

You copying hundreds of sheets? I thought it was only one and it is to a
different workbook unlike the situation in the article.

--
Regards,
Tom Ogilvy



"Chet Shannon" wrote in message
...
I have found a knowledge base article referring to this. It is Copying
worksheet programmatically causes run-time error 1004 in Excel and is
article
number 210684. Microsoft acknowledges this is happening and offers a
work-around of (see pasted in solution)
RESOLUTION
Microsoft provides programming examples for illustration only, without
warranty either expressed or implied. This includes, but is not limited
to,
the implied warranties of merchantability or fitness for a particular
purpose. This article assumes that you are familiar with the programming
language that is being demonstrated and with the tools that are used to
create and to debug procedures. Microsoft support engineers can help
explain
the functionality of a particular procedure, but they will not modify
these
examples to provide added functionality or construct procedures to meet
your
specific requirements. To resolve this problem, save and close the
workbook
periodically while the copy process is occurring, as in the following
sample
code: Sub CopySheetTest()
Dim iTemp As Integer
Dim oBook As Workbook
Dim iCounter As Integer

' Create a new blank workbook:
iTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set oBook = Application.Workbooks.Add
Application.SheetsInNewWorkbook = iTemp

' Add a defined name to the workbook
' that RefersTo a range:
oBook.Names.Add Name:="tempRange", _
RefersTo:="=Sheet1!$A$1"

' Save the workbook:
oBook.SaveAs "c:\test2.xls"

' Copy the sheet in a loop. Eventually,
' you get error 1004: Copy Method of
' Worksheet class failed.
For iCounter = 1 To 275
oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
'Uncomment this code for the workaround:
'Save, close, and reopen after every 100 iterations:
If iCounter Mod 100 = 0 Then
oBook.Close SaveChanges:=True
Set oBook = Nothing
Set oBook = Application.Workbooks.Open("c:\test2.xls")
End If
Next
End Sub


--
Chet Shannon


"Chet Shannon" wrote:

Hi,
Am getting an error that says 'copy method of worksheet class failed".
My
code is
Windows("2006-01-OAKAM-MOP.xls").Activate
Worksheets("OAKAM Facility Diagram").Activate
Sheets("OAKAM Facility Diagram").Copy Befo=Workbooks( _
"2006-02-OAKAM-MOP.xls").Worksheets("AM Flight Schedule")

Is there a workaround or am I doing something wrong? I am just copying
a
worksheet from workbook A to workbook B.

Thanks,
Chet
--
Chet Shannon