copy method of worksheet class failed: trying to copy a hidden she

[sam]

I got a "copy method of worksheet class failed" error when trying to copy
sheet2.
Sheet2 is hidden.

here is the code:

Sheets("Sheet2").Copy

Thanks in advance

 

[JLGWhiz]

You will get that message when you copy a hidden sheet without specifying
Before:= or After:=. If the sheet is not hidden, it will create a new
workbook with only that sheet in it.

 

[Don Guillett]

If you goto the doctor and say "it hurts when I do this", the doctor might
say "Dont do that"

 

[JLGWhiz]

P.Sl

For best results, unhide the worksheet to copy it, then re-hide it if
needed.
Otherwise, you can get some strange results.

 

[Chip Pearson]

When you don't specify a parameter after the Copy method, a copy of
the worksheet is created as the only worksheet in a newly created
workbook. However, a workbook must contain at least one visible
worksheet. Your code fails because it instructs Excel to create a new
workbook with no visible worksheet. Try

Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Sheet2")
.Visible = True
.Copy
.Visible = False
''''
' At this point, the newly created workbook containing
' only Sheet2 is the visible ActiveWorkbook. To return
' back to the original workbook, uncomment the line below:
''''
'.Parent.Activate
End With
Application.ScreenUpdating = True


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)