Removing a Private Sub

Dublin

New Member
Joined
Jan 8, 2004
Messages
5
Hi,

I have an original sheet that has a splash screen everytime someone clicks on it.
Private Sub worksheet_activate()
Bank_Intro.Show
End Sub

The user clicks a macro which copies the original sheet to a new sheet and they carry out their work on the new copy version.

Problem.
What I need is for the splash screen NOT to show on the copy sheet. How do I stop the Private Sub being copied over from the original to the copied version. I can include the code in the macro for creating the copy sheet.

Thanks
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
if the copy is the exact duplicate of the original one, then
the splashscreen is included.
care to post the code that makes the copy. little revision
may improve that!
 

Dublin

New Member
Joined
Jan 8, 2004
Messages
5
Sure, here is the code. It simply copies the original ("PY-PC-X-X-04") to a new sheet within the same workbook.


Sub New_Template()
'
' New_Template Macro

Sheets("PY-PC-X-X-04").Select
Sheets("PY-PC-X-X-04").Copy Before:=Sheets(2)

End Sub


Cheers!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Instead of copying the sheet, copy all the cells, like this:

Code:
Sub Test()
    Dim Sh As Worksheet
'   Cjange name of sheet to suit
    Set Sh = Worksheets("Sheet1")
    Worksheets.Add
    Sh.Cells.Copy ActiveSheet.Range("A1")
End Sub
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
try this
since you are copy the whole sheet, comes with it
all it has.
so my workaround in this is to filter the sheet name that
can use the splash screen

Private Sub worksheet_activate()
if activesheet.name ="PY-PC-X-X-04" then
Bank_Intro.Show
end if
End Sub

this will prevent the intro to be displayed if the activesheet is not
sheet "PY-PC-X-X-04"
because the copy is anme

sheet "PY-PC-X-X-04(1)"
 

Dublin

New Member
Joined
Jan 8, 2004
Messages
5
Sixth Sense.. there's a pint on the table in Dublin waiting for you!

Cheers works a treat..
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,537
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top