Copy hidden sheet and then make copy visible HELP?

[quidsin]

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

 

[Guest]

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.

 

[Jim Cone]

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" <[email protected]>
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.

 

[Guest]

Can I explain it - no.

Have I seen it - yes

As I recall, you can even have a sheet in an addin as the activesheet.

I would suspect that to some extent, this is to support the scenario you
describe (of copying a hidden sheet). I wouldn't try to use it too much
past that.

 

[Jim Cone]

Tom,
I think I will stay away from it.
Appreciate your comments.
--
Jim Cone
San Francisco, USA



"Tom Ogilvy"
<[email protected]>
wrote in message
Can I explain it - no.

Have I seen it - yes
As I recall, you can even have a sheet in an addin as the activesheet.

I would suspect that to some extent, this is to support the scenario you
describe (of copying a hidden sheet). I wouldn't try to use it too much
past that.

 

[quidsin]

Tom,
I think I will stay away from it.
Appreciate your comments.
--
Jim Cone
San Francisco, USA


- Show quoted text -

Thanks for the pointers, however still get issues. With;

Sub Make_PtP()

Sheets("PtP (0)").Visible = 0
Sheets("PtP (0)").Copy After:=Sheets(4)
'Copy the PtP Sheet

Sheets("PtP (0)").Visible = 2
Sheets("Gen_Data").Visible = 2
Sheets(5).Visible = -1

End Sub

I have four sheets, before this Sub is run, Sheets(1) is visible,
Sheets(2) or ("Gen_Data") is very hidden and Sheets("PtP (0))") &
("PtMP (0)") (3 and 4 respectively) are very hidden. Now I, assume,
the above would result in ("PtP (0)") being set to hidden, a new sheet
being created (from the "PtP (0)") copy and this then placed at the
end of the current 4 sheets in the WB (i.e. and therefore now becomes
Sheet number 5??) and then it is made visible....but it retains its
hidden setting......why is this?

ST