Hidden worksheet problem

  • Thread starter Thread starter Patrick C. Simonds
  • Start date Start date
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.
 
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
 
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
 
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
 
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"?
 
The code shouldn't fail. Only the new sheet gets renamed. The original
"Master" sheet is still hidden and doesn't get renamed.
 
I am afraid on my system the hidden worksheet "Master" does in fact get
renamed. It gets renamed to "Master (2)"
 
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.
 
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.
 
Back
Top