Copy sheet problem

[SiriS]

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.aspx?scid=kb;en-us;177634&Product=xlw).

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

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

How to copy everything in the sheet?

Thank you.

Siri

 

[Tom Ogilvy]

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 Before:=Sheets(1)
Sheets("Mysheet").Cells.Copy Destination:=Activesheet.Cells

 

[Bob Phillips]

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)

 

[Paulw2k]

Hi SiriS,

This should solve it.

Sub CopyToTopSheet()
Dim Rng As Range

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

End Sub

Regards

Paul

 

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

Hi SiriS,

This should solve it.

Sub CopyToTopSheet()
Dim Rng As Range

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

End Sub

Regards

Paul

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.aspx?scid=kb;en-us;177634&Product=xlw).

 

[SiriS]

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

 

[Tom Ogilvy]

My second suggestion would solve that problem.

 

[SiriS]

Hi Tom Ogilvo,

do you mean by writing

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

?

But I can not use

Sheets("Mysheet").copy Before:=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

 

[Tom Ogilvy]

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

 

[SiriS]

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

 

[c162431]

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!