Copy sheet problem

  • Thread starter Thread starter SiriS
  • Start date Start date
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
 
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)
 
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
 
Actually that just raises an error because of your improper use of
parentheses around the Range("A1").

--
Regards,
Tom Ogilvy

Paulw2k said:
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




SiriS > said:
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
Click to expand...
http://support.microsoft.com/default.aspx?scid=kb;en-us;177634&Product=xlw).
Click to expand...
 
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
 
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
 
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
 
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!
 
Back
Top