Results 1 to 5 of 5

Excel 2010 - Copying sheet with protection manually, protection stays... not with VBA

Threaded View

  1. #1
    Registered User
    Join Date
    04-24-2008
    Posts
    85

    Excel 2010 - Copying sheet with protection manually, protection stays... not with VBA

    Hello,
    When I manually copy a sheet using VBA the protection on it stays, which is exactly what I want.
    However, when I do this using VBA, the protection disappears.

    I need to have the protection remain, it's initially applied in the ThisWorkbook module as it is special in order to allow a user to expand/collapse groups.

    I'd like to have this protection copied to all of my sheets.

    The sheets are created based off of a template, this template is the one with the protection and because they don't exist before, I can't just add them into the code for when the workbook opens.

    Any ideas?

    Here are the code snippets:
    Private Sub Workbook_Open()
    With Worksheets("Template S")
           .Protect Userinterfaceonly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
           .EnableOutlining = True
    End With
    End Sub
    and
    Dim I As Integer
    For I = 1 To Range("CountCC").Value
        
    Dim MySheetNameS As String
    MySheetNameS = Range("CC_" & I).Value & " S"
    Sheets("Template S").Copy After:=Sheets(Worksheets.Count)
    ActiveSheet.Name = MySheetNameS
        
    Next I
    Any help would be appreciated.
    Thanks
    Last edited by AdamParker; 03-02-2012 at 12:28 PM. Reason: Solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1