Hidden worksheet problem

P

Patrick C. Simonds

I would like to create a worksheet and call it Master and have the worksheet
hidden. What I need is a routine which will create a copy of the hidden
worksheet.
 
P

Per Jessen

Hi

Try this:

Sub CopyHiddenSheet()
'Sheets("Master").Visible = False

Sheets("Master").Copy After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Name = "Master" & Sheets.Count
.Visible = True
End With
End Sub

Regards,
Per
 
S

signon77

I would like to create a worksheet and call it Master and have the worksheet
hidden. What I need is a routine which will create a copy of the hidden
worksheet.
Click to expand...

Hi Patrick,

My suggestion is that you base your solution on code such as this:

*****************************************************************************************
Sub test()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
If ws.Visible = xlSheetHidden Then ws.Copy
after:=Worksheets("Sheet3")
Next ws

End Sub
*******************************************************************************************

Rob
 
J

Joel

The problem is recognizing the new sheet and making it visible. Try this code.

With ThisWorkbook
.Worksheets("Master").Copy after:=.Worksheets(.Worksheets.Count)
Worksheets(Worksheets.Count).Visible = True
End With
 
P

Patrick C. Simonds

Thanks.

This does just about almost what I need it to. One problem though is that it
renames the hidden worksheet. It changes it from Master to Master (2) which
means of course, the next time the code is run it fails because it is
looking for Master.

So is there any way to prevent this and to give the newly created worksheet
the name "Blank"?
 
J

Joel

The code shouldn't fail. Only the new sheet gets renamed. The original
"Master" sheet is still hidden and doesn't get renamed.
 
O

ordnance1

I am afraid on my system the hidden worksheet "Master" does in fact get
renamed. It gets renamed to "Master (2)"
 
J

Joel

Each worksheet has two different names (sheet name, codename). The best way
of seeing this is in the VPA Project Explorer. Go to VBA Editor (Alt-F11).
The select from menu View - Project Explorer. You will see all the sheets
hidden and visible. You will also see both sets of names. The left set of
names will match the Tab Names on the Worksheets. The Right (codename) set
may or may not match. I believe the Master (2) you are seeing is the
codename (right set of names). The codename is read only.
 
P

Patrick C. Simonds

Sir

Thanks for your time and information. I discovered my problem. The
worksheet called Master, was not sheet1. Once I changed it to sheet1
everything worked just as you said it would.
 
Top