Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Unprotect to Copy (Excel 2002)

    Hi

    I have the Macro below which works fine, however I would like to protect sheets "Chemicals" and "Installation Request" therefore I would like to adjust the macro to (Unprotect) copy and then (Re-protect) on completion.

    Many thanks in advance

    Braddy

    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 30/10/2003 by MD1302
    Sheets(Array("Chemicals", "Installation Request")).Select
    Sheets("Chemicals").Activate
    Sheets(Array("Chemicals", "Installation Request")).Copy
    Sheets(Array("Chemicals", "Installation Request")).Select
    Sheets("Chemicals").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    Range("B2").Select
    Sheets("Installation Request").Select
    ActiveWindow.WindowState = xlMinimized
    Sheets("Installation Request").Select
    Range("M3").Select
    End Sub
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unprotect to Copy (Excel 2002)

    Before the line Cells.Select, insert these lines:

    Sheets("Chemicals").Unprotect
    Sheets("Installation Request").Unprotect

    After the line with PasteSpecial, insert these lines:

    Sheets("Chemicals").Protect
    Sheets("Installation Request").Protect

    If the sheets have been protected with a password, say "terces", add a space followed by Password:="terces" to each of the above lines.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    <P ID="edit" class=small>(Edited by Rudi on 16-Feb-05 14:11. Oops, you only want the two sheets protected. Only saw that now!... I modified it>)</P>Try this:

    Option Explicit

    Sub Macro3()
    Dim i As Integer
    Application.ScreenUpdating = False
    For i = 1 To Sheets.Count
    Sheets(i).Unprotect
    Next i
    Sheets(Array("Chemicals", "Installation Request")).Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    Range("B2").Select
    Sheets("Installation Request").Select
    ActiveWindow.WindowState = xlMinimized
    ActiveWindow.WindowState = xlMaximized
    Sheets("Installation Request").Select
    Range("M3").Select
    Sheets("Chemicals").Protect
    Sheets("Installation Request").Protect
    Application.ScreenUpdating = True
    End Sub
    Regards,
    Rudi

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    Hi Hans

    Thanks to you and Rudi for your prompt reply, I will test it as soon as I can and get back to you.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    Hi Rudi

    My grateful thanks to you and Hans both options worked equally aswell.

    Once again I am in your debt.

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unprotect to Copy (Excel 2002)

    Your code unprotects all sheets but only reprotects two. Your code could also cause problems if there is a mix of protected and unprotected sheets in the workbook.
    Legare Coleman

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    You are correct Legare. My code will also unprotect sheets that should have remained protected. The best would have been to only unprotect the 2 sheets that needed to be copied. Hans did cover that in his post.

    Thx for highlighting that! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    Hi Hans

    I tried to apply the code you gave me to another sheet which needs to Unprotect and Protect 11 Sheets,could I impose on you to tell where I have gone wrong.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unprotect to Copy (Excel 2002)

    You group the sheets in the new workbook, then try to unprotect them. You can't protect or unprotect sheets while they are grouped. See if changing the order of the instructions works (attached)

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    Hi Hans

    I not quite sure what you mean by grouping, but I tried your new code and it got as far as far as 8 sheets and then stopped at unprotect Sheets("Istallations").Unprotect Password:="Secret".


    Regards Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    Hi Hans

    After reading Legares post I realised he was correct as, I had used Rudi's Code I decide to go with yours for the two sheet Unprotect / Protect but get the attached debug.

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unprotect to Copy (Excel 2002)

    Sheets(Array("Chemicals", "Installation Request")).Select
    Sheets(Array("Chemicals", "Installation Request")).Copy
    Sheets("Chemicals").Unprotect Password:="Secret"
    Sheets("Istallation Request").Unprotect Password:="Secret"
    Sheets(Array("Chemicals", "Installation Request")).Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Range("B2").Select
    Sheets("Installation Request").Select
    Sheets("Chemicals").Protect Password:="Secret"
    Sheets("Istallation Request").Protect Password:="Secret"
    ActiveWindow.WindowState = xlMinimized
    Sheets("Installation Request").Select
    Range("M3").Select

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    Braddy, i'm sorry to have lead you astray. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    <img src=/S/please.gif border=0 alt=please width=31 height=23> forgive me!

    Maybe you could view some lines in that code I sent as a sample to clean up the macro and make it run faster. The line Application.ScreenUpdating = True/False is useful, and also the fact that in some occations it is not necessary to select an object (sheet) to perform an action on it! --- (for what its worth, its my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    Hi Hans

    This code worked form me in the end I put Unprotect at the beginning and Protect at the end.

    Once again thanks for your assistance.

    Braddy

    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 30/10/2003 by Alan Bradshaw
    Sheets("Chemicals").Unprotect Password:="Secret"
    Sheets("Installation Request").Unprotect Password:="Secret"
    Sheets(Array("Chemicals", "Installation Request")).Select
    Sheets("Chemicals").Activate
    Sheets(Array("Chemicals", "Installation Request")).Copy
    Sheets(Array("Chemicals", "Installation Request")).Select
    Sheets("Chemicals").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    Range("B2").Select
    Sheets("Installation Request").Select
    ActiveWindow.WindowState = xlMinimized
    Sheets("Installation Request").Select
    Range("M3").Select
    Sheets("Chemicals").Protect Password:="Secret"
    Sheets("Installation Request").Protect Password:="Secret"

    End Sub
    If you are a fool at forty, you will always be a fool

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unprotect to Copy (Excel 2002)

    Hi Rudi

    No need to apologise , as you can see in my reply to Hans , I just put Unprotect and the beginning of the Macro Protect at the end and got a result.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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