+ Reply to Thread
Results 1 to 11 of 11

Macro to unlock sheet, copy to new workbook, then re-lock original sheet

  1. #1
    Registered User
    Join Date
    03-07-2015
    Location
    Charlotte, NC
    MS-Off Ver
    2013
    Posts
    11

    Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    Hi,
    I have an Excel 2013 sheet with a button that unlocks a sheet and saves that sheet without the formulas and button, (values only), to a new workbook then locks the new sheet but it leaves the original sheet unlocked. I need a way for it to also re-lock the original sheet. I'm just trying to dummy proof this for someone that keeps deleting stuff.
    Big thanks to those who have helped me get this far with it.
    This is my macro so far:
    Please Login or Register  to view this content.
    Thanks,
    Foos Master
    "Foosball is Therapy"

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    You have to call out the sheet, ex:
    Please Login or Register  to view this content.
    Change Sheet2 to the name of the sheet you want to protect.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Registered User
    Join Date
    03-07-2015
    Location
    Charlotte, NC
    MS-Off Ver
    2013
    Posts
    11

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    gmr4evr1,
    I just can't figure out how to do that. I keep getting errors with the ways I try to put the name in. I'm kinda new to this. My original workbook name is "FT" and the sheet name is "4T-Copy". I also need it to save with my password the way it saves the new sheet with: ActiveSheet.Protect "Password"
    I have tried several different ways of naming it but just can't get it to work.
    Thanks for any suggestions,
    Foos Master

  4. #4
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    My bad, you can just use the sheet name, I.E. Sheet1, sheet2 etc. whichever sheet the 4T-Copy is. For the password, I think you just replace True with "Password". Like you did with the AcviteSheet line of code.
    Last edited by gmr4evr1; 03-08-2015 at 07:06 PM. Reason: Correct typo

  5. #5
    Registered User
    Join Date
    03-07-2015
    Location
    Charlotte, NC
    MS-Off Ver
    2013
    Posts
    11

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    gmr4evr1,
    Sorry, I just can’t get it to work. I tried using (Sheet8.Protect "Password") and (4T-Save.Protect “Password”) but I still get an error. The sheet is the 8th in line on that workbook but maybe not the 8th sheet I originally created in that workbook, not sure how to find the actual sheet number if it is the number of the sheet created.
    Foos Master

  6. #6
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    In the VBA editor property window it should show the sheet #'s next to the name of the sheet which would be in () on the left side, so, Sheet# (4T-Copy).
    Where are you placing the code?
    Last edited by gmr4evr1; 03-08-2015 at 07:31 PM.

  7. #7
    Registered User
    Join Date
    03-07-2015
    Location
    Charlotte, NC
    MS-Off Ver
    2013
    Posts
    11

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    It was sheet 17, my bad, I should have looked on the editor tree. BIG thanks. This is a Double Elimination Foosball Tournament Workbook for use in places where there is no internet access for using the Netfoos online software. Again, BIG thanks for the help.

    Foos Master,
    "Foosball is Therapy"

  8. #8
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    Not a problem, glad you got it worked out. And good luck!

  9. #9
    Forum Contributor
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    349

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    Please click the * Add Reputation if this helps
    If solved remember to mark Thread as solved

    "I'm glad to help and this is not meant to sound smart, but either you have super-human vision to see all those controls cleared one by one with the code I posted, or your computer is really slow."

  10. #10
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    By the way, you might want to check the other sheets, like sheet8, you might need to unprotect it

  11. #11
    Registered User
    Join Date
    03-07-2015
    Location
    Charlotte, NC
    MS-Off Ver
    2013
    Posts
    11

    Re: Macro to unlock sheet, copy to new workbook, then re-lock original sheet

    Blokeman,
    The other thread solved 1 of 2 issues. I figured a new thread would solve the other. Sorry if I did something wrong, it was not intended. I marked the other thread solved and put the link to this thread for final solution. I hope that is OK.

    Foos Master
    "Foosball is Therapy"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to auto-unlock a worksheet and then re-lock that sheet
    By Foos Master in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-08-2015, 09:14 PM
  2. [SOLVED] Macro to copy cell width to new sheets (ie copy original sheet format)
    By teenyjem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-20-2013, 07:28 AM
  3. Macro to copy Dyanmic lists then copy sheet and clear the original data
    By twiggywales in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2012, 01:06 PM
  4. [SOLVED] Create Macro to lock and save workbook and another to unlock
    By Nicola13 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-03-2012, 12:23 PM
  5. i want to lock a tab in excel, and unlock in another sheet
    By john tempest in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2005, 01:10 PM

Tags for this Thread

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