Trouble with copy of xlSheetVeryHidden

[Casey]

Hi,
Below is code for copying a hidden worksheet into the same Workbook. It
works fine as long as the sheet is just hidden, however when I attempted
to change the sheets property to xlSheetVeryHidden, the code fails with
the following error message:

Run-time error '1004'
Method 'copy'of object'_worksheet_failed

Here is the code.

Sub Add_New_RFI()
Dim CopySht As Worksheet

Set CopySht = Worksheets("(0)")

Application.ScreenUpdating = False
With CopySht
..Copy After:=Sheets(ThisWorkbook.Sheets.Count)
End With
ActiveSheet.Visible = True
Application.ScreenUpdating = True
End Sub

Thanks in advance.

 

[Tim Williams]

With CopySht
.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
End With

forgot the "." before Copy

Tim

 

[Casey]

Tim,
Thanks for the reply. But I'm confused. My code shows a Period befor
Copy. Did I misunderstand your post?
Maybe I was too cryptic in my first explaination.
I'm using a hidden sheet as a "template" that is copied and mad
visable as a new worksheet.
The code works fine as long as the CopySht is only hidden, however whe
the CopySht's property is set to xlVeryHidden the code no longer work
and give the error message found in my original post.
Thanks again for your help

 

[Dave Peterson]

I think that when you post through excelforum, that those leading dots can get
eaten up (and your indentation looks pretty funny, too--but I'm not sure if
that's your code or excelforum).

Anyway, this worked ok for me:


Option Explicit
Sub Add_New_RFI2()
Dim CopySht As Worksheet
Dim myVis As Long

Set CopySht = Worksheets("(0)")

Application.ScreenUpdating = False
With CopySht
myVis = .Visible
.Visible = xlSheetVisible
.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
.Visible = myVis
End With

Application.ScreenUpdating = True
End Sub

 

[Casey]

Dave,
Thank you. The code worked great. I really like the Excel Forum,
however, the indents do seem to get lost. I really like building little
apps for the rest of the crew when I have time and want to learn the
best practices. Would it be better to post directly on the public
forums, rather than here? I just copy and paste my code out of the
editor into the thread dialog, do the public forums handle that better,
meaning keeping the formatting?
I'm a part-time programmer but a full time Construction estimator and
project manager, and as limited as my skills are, the resident geek at
work. I can't see an end to the possibilities for my industry.

Thanks again

 

[Tim Williams]

Casey,

If you have either outllook express or outlook then you might be better off
connecting directly to microsoft.public.excel.programming than using one of
the web-based interfaces.
You'll find it a better experience.

Tim

 

[Dave Peterson]

I agree with Tim. I don't like the web interface.

But do remember google for finding old posts (and answers to common questions).

 

[Jon Peltier]

Full blown Outlook doesn't work as a newsreader. Outlook Express works,
so does Netscape and a few more.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______