Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hidden sheet and then make copy visible HELP?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hidden sheet and then make copy visible HELP?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hidden sheet and then make copy visible HELP?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hidden sheet and then make copy visible HELP?
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. -- Regards, Tom Ogilvy "Jim Cone" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hidden sheet and then make copy visible HELP?
Tom, I think I will stay away from it. Appreciate your comments. -- Jim Cone San Francisco, USA "Tom Ogilvy" 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. -- Regards, Tom Ogilvy "Jim Cone" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hidden sheet and then make copy visible HELP?
On 6 Aug, 19:59, "Jim Cone" wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Visible Cells in Sheet with Merged and Hidden Cells | Excel Discussion (Misc queries) | |||
Copy a whole Word doc into Excel sheet & keep it all visible? | Excel Discussion (Misc queries) | |||
copy to hidden sheet | Excel Programming | |||
Need Macro to Copy Visible Cells to New Sheet | Excel Programming | |||
Need Macro to Copy Visible Cells to New Sheet | Excel Programming |