Copy method of worksheet class failed - help!

[Chet Shannon]

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 Before:=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

[Tom Ogilvy]

All you need is:

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

--
Regards,
Tom Ogilvy

"Chet Shannon" <[email protected]> wrote in message
news:[email protected]...
> 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 Before:=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

[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 _
> Before:=Workbooks("2006-02-OAKAM-MOP.xls") _
> .Worksheets("AM Flight Schedule")
>
> --
> Regards,
> Tom Ogilvy
>
> "Chet Shannon" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Before:=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
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 _
> > Before:=Workbooks("2006-02-OAKAM-MOP.xls") _
> > .Worksheets("AM Flight Schedule")
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Chet Shannon" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 Before:=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
> >
> >
> >

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 _
> > > Before:=Workbooks("2006-02-OAKAM-MOP.xls") _
> > > .Worksheets("AM Flight Schedule")
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Chet Shannon" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 Before:=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
> > >
> > >
> > >
>
>
>

[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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 _
> > > > Before:=Workbooks("2006-02-OAKAM-MOP.xls") _
> > > > .Worksheets("AM Flight Schedule")
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Chet Shannon" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 Before:=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
> > > >
> > > >
> > > >
> >
> >
> >

[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 Before:=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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 Before:=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

[JNW]

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 _
> > > > > Before:=Workbooks("2006-02-OAKAM-MOP.xls") _
> > > > > .Worksheets("AM Flight Schedule")
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > > "Chet Shannon" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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 Before:=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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

[purpleding]

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(iSheet, 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]

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

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(iSheet, 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

[Jen]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Before:=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
>
>
>