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

You may look to the workbook events in the source and destination workbooks.
You may have some code there that when the workbook is
deactivated/activated/etc. it cancels the copy property.

I have had this problem with some of my workbooks. Just takes time to look
at it and see what is causing the problem

"Chet Shannon" wrote:

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

Help! I got the same error using Excel 2003

Here is my code, and I got this error after it copies 63 times.
It will copy about 110 times.

Please help

sub populateworksheet()
dim totalrow, isheet, rownum, v_col, icol as integer
dim mysht, metadata as worksheet
dim sheetname as string
dim fnd as range
dim v_cell_value as variant

totalrow =
activeworkbook.worksheets(\"tablename\").usedrange .rows.count

for isheet = 1 to totalrow
worksheets(\"template\").copy after:=worksheets(worksheets.count)
set mysht = worksheets(worksheets.count)
sheetname = activeworkbook.worksheets(\"tablename\").cells(ish eet,
1).value
mysht.name = sheetname
mysht.cells(1, 1).value = sheetname
set metadata = worksheets(\"list\")
set fnd = metadata.columns(1l).find(sheetname, lookat:=xlwhole)
rownum = fnd.row
v_col = 5
icol = 1
do while metadata.cells(rownum, v_col).value < \"\"
v_cell_value = metadata.cells(rownum, v_col).value
mysht.cells(2, icol).value = v_cell_value
v_col = v_col + 1
icol = icol + 1
loop
next isheet

end sub


--
purpleding
------------------------------------------------------------------------
purpleding's Profile: http://www.excelforum.com/member.php...o&userid=30580
View this thread: http://www.excelforum.com/showthread...hreadid=499532

Anyone? It's very urgent! Thanks for your help


purpleding Wrote:
Help! I got the same error using Excel 2003

Here is my code, and I got this error after it copies 63 times.
It will copy about 110 times.

Please help

sub populateworksheet()
dim totalrow, isheet, rownum, v_col, icol as integer
dim mysht, metadata as worksheet
dim sheetname as string
dim fnd as range
dim v_cell_value as variant

totalrow
activeworkbook.worksheets(\"tablename\").usedrange .rows.count

for isheet = 1 to totalrow
worksheets(\"template\").cop
after:=worksheets(worksheets.count)
set mysht = worksheets(worksheets.count)
sheetname = activeworkbook.worksheets(\"tablename\").cells(ish eet
1).value
mysht.name = sheetname
mysht.cells(1, 1).value = sheetname
set metadata = worksheets(\"list\")
set fnd = metadata.columns(1l).find(sheetname, lookat:=xlwhole)
rownum = fnd.row
v_col = 5
icol = 1

do while metadata.cells(rownum, v_col).value < \"\"
v_cell_value = metadata.cells(rownum, v_col).value
mysht.cells(2, icol).value = v_cell_value
v_col = v_col + 1
icol = icol + 1
loop

next isheet

end su

--
purpledin
-----------------------------------------------------------------------
purpleding's Profile: http://www.excelforum.com/member.php...fo&userid=3058
View this thread: http://www.excelforum.com/showthread.php?threadid=49953

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