Tom,
I ran into this by accident and it is puzzling to me.
Even though the sheet copied is hidden,
the following code recognizes the copied sheet as the active sheet.
It doesn't if stepped thru but does if it is "run" (F5). . .
Can you shed any light on this behavior?
'--
Sub xxxx()
Dim objNewSheet As Excel.Worksheet
Worksheets("Sheet2").Copy after:=Worksheets(1)
Set objNewSheet = ActiveSheet
objNewSheet.Name = "MushRoom"
objNewSheet.Visible = True
Set objNewSheet = Nothing
End Sub
'--
Jim Cone
San Francisco, USA


"Tom Ogilvy"
wrote in message
Sub Make_PtMP()

Dim Next_PtMP_PageOBJ As Object

Sheets("PtMP (0)").Copy After:=Sheets(1)
With Sheets(2)
.Visible = True
End With

End Sub

worked for me.
--
Regards,
Tom Ogilvy



" wrote:
HI All,
Now I've Googled (a lot!!) on this and can't find the definitive
answer.

I have a hidden (not very-hidden) worksheet lets call it "Details
(0)". This is a template I want to remain hidden.

Now the reason I called it "Details (0)" is because, when you copy it
(via VBA code on a button), the subsequent sheets become "...(1)" "....
(2)" etc. etc. which are ready for the user to fill in.

I'm having real problems leaving the zero sheet hidden and copying it
and then making the copy visible. I don't want to make the zero sheet
visible, copy it then re-hide it. That's messy. Also I want to leave
Excel to automatically numbers it, rather than me managing the
numbering.

The thing is that I note that whilst you can copy a hidden sheet,
can't then select it to make it visible. Also as Excel has just
created the new sheet and (Iassume) named it, why can't I "get" that
name to make it visible without selecting it (which, as stated, I
can't do anyway).

e.g.

Sub Make_PtMP()

Dim Next_PtMP_PageOBJ As Object

Set Next_PtMP_PageOBJ = Sheets("PtMP (0)").Copy
With Next_PtMP_PageOBJ
.Visible = True
.Move After:=Sheets(1)
End With

End Sub.

.....but this fails on Set Next_PtMP_PageOBJ = Sheets("PtMP (0)").Copy
(more hope than judgement!)

Any thoughts????

ST