prevent copy sheet inside a workbook

I created a costing workbook consisting of several worksheets; there is one particular sheet that is the template, it is worksheet protected but user can input data to it, what I want is to prevent t...

excel

JKPieterse

Mar 29, 2018

You could try this code in ThisWorkbook:

Option Explicit

Dim msOldsheet As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name Like msOldsheet & " (#)" Or Sh.Name Like msOldsheet & " (##)" Then
        MsgBox "Copying this sheet is not allowed!"
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        msOldsheet = ""
        Sh.Delete
        Application.DisplayAlerts = True
        Application.EnableEvents = True
    End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'Add a test here to check if this is the sheet you didn't want copied
    If Sh.Name = "TheSheetNotToCopy" Then
        msOldsheet = Sh.Name
    End If
End Sub

JKPieterse

Silver Contributor

Mar 29, 2018

You could try this code in ThisWorkbook:

Option Explicit

Dim msOldsheet As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name Like msOldsheet & " (#)" Or Sh.Name Like msOldsheet & " (##)" Then
        MsgBox "Copying this sheet is not allowed!"
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        msOldsheet = ""
        Sh.Delete
        Application.DisplayAlerts = True
        Application.EnableEvents = True
    End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'Add a test here to check if this is the sheet you didn't want copied
    If Sh.Name = "TheSheetNotToCopy" Then
        msOldsheet = Sh.Name
    End If
End Sub

Lorenzo Kim

Bronze Contributor

Mar 29, 2018

thank you for your reply

Forum Discussion

prevent copy sheet inside a workbook

Resources