Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2005
    Location
    Nairobi, Nairobi, Kenya
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Copying a protected Worksheet - Excel 2007

    I have a spreadsheet in Excel 2007. I can protect the cells that contain formulas. However when the worksheet gets copied (we use a new worksheet for each month but it is contained in the same workbook) the protection is lost. That means I have to relock/protect each spreadsheet each month. Is there an easier way of doing this? I have seen some information that you can only 'automate' this process with a macro. Any help would be appreciated.

    Bob

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bob,

    Sorry I can't test this in 2007 but in 2010 the cell protections are copied along with the sheet protection setting to the new copy of the sheet. Now let me ask you a question. How are you coping the sheet? If you select the cells and copy & paste the behavior you mention is what happens. However, if you right click on the sheet tab and select Move or Copy... and select "Create a copy" the behavior you desire should be executed as it is in 2010.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    RDPaterson (2012-03-08)

  4. #3
    Lounger
    Join Date
    Mar 2005
    Location
    Nairobi, Nairobi, Kenya
    Posts
    37
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I saw the answer, tried it and it worked. But I have been remiss and not saying thank you. So Thank You.

    Bob

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    To answer RG, it does work the same in Excel2007.

    Being lazier than RG, instead of right-clicking the tab and everything else, to copy a sheet I just press the [Ctrl] key with my left-hand while I point-and-drag the sheet tab to be copied to it's new location. Then double-click the tab to rename it.

    zeddy

Tags for this Thread

Posting Permissions

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