Removing a Private Sub

[Dublin]

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

 

[SIXTH SENSE]

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]

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]

Instead of copying the sheet, copy all the cells, like this:

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]

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]

Sixth Sense.. there's a pint on the table in Dublin waiting for you!

Cheers works a treat..