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