Hello,
I've got the run-time error "Copy method of worksheet class failed"
and is trying to use the MSDN workaround for this problem(se
http://support.microsoft.com/default...4&Product=xlw).

But in the MSDN workaround they only copy cells, I want to cop
everything in the sheet, format also.

Sheets.Add Befo=Sheets(1)
Sheets("Mysheet").Cells.Copy
Sheets(ActiveSheet.Name).Paste

How to copy everything in the sheet?

Thank you.

Siri

--
Message posted from http://www.ExcelForum.com

the code you show does copy everything in on the sheet. It will copy
formats and so forth. an alternative would be


Sheets("Mysheet").copy Befo=Sheets(1)
Sheets("Mysheet").Cells.Copy Destination:=Activesheet.Cells

--
Regards,
Tom Ogilvy



"SiriS " wrote in message
...
Hello,
I've got the run-time error "Copy method of worksheet class failed",
and is trying to use the MSDN workaround for this problem(see

http://support.microsoft.com/default...4&Product=xlw).

But in the MSDN workaround they only copy cells, I want to copy
everything in the sheet, format also.

Sheets.Add Befo=Sheets(1)
Sheets("Mysheet").Cells.Copy
Sheets(ActiveSheet.Name).Paste

How to copy everything in the sheet?

Thank you.

SiriS


---
Message posted from http://www.ExcelForum.com/

Cells.Copy copies everything in the sheet. Cells refers to all of the cells
on a worksheet.

Have you tried this technique?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"SiriS " wrote in message
...
Hello,
I've got the run-time error "Copy method of worksheet class failed",
and is trying to use the MSDN workaround for this problem(see

http://support.microsoft.com/default...4&Product=xlw).

But in the MSDN workaround they only copy cells, I want to copy
everything in the sheet, format also.

Sheets.Add Befo=Sheets(1)
Sheets("Mysheet").Cells.Copy
Sheets(ActiveSheet.Name).Paste

How to copy everything in the sheet?

Thank you.

SiriS


---
Message posted from http://www.ExcelForum.com/

Hi SiriS,

This should solve it.

Sub CopyToTopSheet()
Dim Rng As Range

Sheets.Add Befo=Sheets(1)
Set Rng = Sheets("MySheet").Cells
Rng.Copy (Range("A1")) 'of the sheet just added.

End Sub

Regards

Paul




"SiriS " wrote in message
...
Hello,
I've got the run-time error "Copy method of worksheet class failed",
and is trying to use the MSDN workaround for this problem(see

http://support.microsoft.com/default...4&Product=xlw).

But in the MSDN workaround they only copy cells, I want to copy
everything in the sheet, format also.

Sheets.Add Befo=Sheets(1)
Sheets("Mysheet").Cells.Copy
Sheets(ActiveSheet.Name).Paste

How to copy everything in the sheet?

Thank you.

SiriS


---
Message posted from http://www.ExcelForum.com/

Actually that just raises an error because of your improper use of
parentheses around the Range("A1").

--
Regards,
Tom Ogilvy

"Paulw2k" wrote in message
...
Hi SiriS,

This should solve it.

Sub CopyToTopSheet()
Dim Rng As Range

Sheets.Add Befo=Sheets(1)
Set Rng = Sheets("MySheet").Cells
Rng.Copy (Range("A1")) 'of the sheet just added.

End Sub

Regards

Paul




"SiriS " wrote in message
...
Hello,
I've got the run-time error "Copy method of worksheet class failed",
and is trying to use the MSDN workaround for this problem(see


http://support.microsoft.com/default...4&Product=xlw).

But in the MSDN workaround they only copy cells, I want to copy
everything in the sheet, format also.

Sheets.Add Befo=Sheets(1)
Sheets("Mysheet").Cells.Copy
Sheets(ActiveSheet.Name).Paste

How to copy everything in the sheet?

Thank you.

SiriS


---
Message posted from http://www.ExcelForum.com/

Sorry, I did not explain correctly. My problem is I have buttons, graph
and drawingobjects in my sheet that I would like to copy, not just th
cells; I do want to copy everything in the sheet.

Do I have to do like this:

Sheets.Add Befo=Sheets(1) 'Copy Cells
Sheets("Mysheet").Cells.Copy
Sheets(ActiveSheet.Name).Paste
Sheets("Mysheet").DrawingObjects.Copy 'Copy drawingobjects
Sheets(ActiveSheet.Name).Paste
....
'Copy graphs etc.

and so on, for every type of object in the sheet?


Regards
Siri

--
Message posted from http://www.ExcelForum.com

My second suggestion would solve that problem.

--
Regards,
Tom Ogilvy


"SiriS " wrote in message
...
Sorry, I did not explain correctly. My problem is I have buttons, graphs
and drawingobjects in my sheet that I would like to copy, not just the
cells; I do want to copy everything in the sheet.

Do I have to do like this:

Sheets.Add Befo=Sheets(1) 'Copy Cells
Sheets("Mysheet").Cells.Copy
Sheets(ActiveSheet.Name).Paste
Sheets("Mysheet").DrawingObjects.Copy 'Copy drawingobjects
Sheets(ActiveSheet.Name).Paste
...
'Copy graphs etc.

and so on, for every type of object in the sheet?


Regards
SiriS


---
Message posted from http://www.ExcelForum.com/

Hi Tom Ogilvo,

do you mean by writing

Sheets("Mysheet").copy Befo=Sheets(1)
Sheets("Mysheet").Cells.Copy Destination:=Activesheet.Cells

?

But I can not use

Sheets("Mysheet").copy Befo=Sheets(1),

since that will raise the run-time error "Copy method of workshee
class failed" I mentioned in my first message (the run-time erro
occurs when I have copied the sheet many times in a loop, se
Microsofts Knowledge Base-artikel - 177634 ).

Regards,
Siri

--
Message posted from http://www.ExcelForum.com

Sirio
Are you actually using xl97 and are you sure that is your problem?

--
Regards,
Tom Ogilvy

"SiriS " wrote in message
...
Hi Tom Ogilvo,

do you mean by writing

Sheets("Mysheet").copy Befo=Sheets(1)
Sheets("Mysheet").Cells.Copy Destination:=Activesheet.Cells

?

But I can not use

Sheets("Mysheet").copy Befo=Sheets(1),

since that will raise the run-time error "Copy method of worksheet
class failed" I mentioned in my first message (the run-time error
occurs when I have copied the sheet many times in a loop, see
Microsofts Knowledge Base-artikel - 177634 ).

Regards,
SiriS


---
Message posted from http://www.ExcelForum.com/

No, I'm using Excel 2002.

I do not know what else could be my problem? Since I get the run-tim
error 1004 "Copy method of worksheet class failed".

(I know the knowledge article says the problem only occur fo
Excel97).

Regards,
Siri

--
Message posted from http://www.ExcelForum.com

Hi all,

I think I have a similar problem as SiriS.

Part of my coding is as follows:

ChDir "C:\Done"
FDir = "C:\Done\"
Workbooks.Open FileName:=FDir + GetFileName(x)
Windows(GetFileName(x)).Activate

Sheets("NewConds").Select
Cells.Select
Selection.Copy

Windows("New Book.xls").Activate
Sheets("NewConds").Select
Cells.Select
ActiveSheet.Paste

When the Macro ran to the line ActiveSheet.Paste, an error message will
be prompted as follows:

Run-time error '-2147417848 (80010108)':
Method 'Paste' of object '_worksheet' failed

In fact, I don't know why it happened. I use these codings before and
they are Ok. Same as SiriS, I am using Excel 2002.

Can someone here help us to solve the problem? Thanks!


---
Message posted from http://www.ExcelForum.com/