Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    worksheet unprotect (office2002 widowsxp)

    how can i unprotect a worksheet (password ABC ) copy last cell in collum a to a new worksheet and copy a row of data back to original sheet and password on closing file

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

    Re: worksheet unprotect (office2002 widowsxp)

    By "a new worksheet", do you really mean that you want to create a new blank sheet, or do you mean an already existing sheet?
    Where should the last (populated) cell in column A be copied to in the "new" sheet?
    You say "copy a row of data back to original sheet". Where does this row come from and where should it be copied in the original sheet?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: worksheet unprotect (office2002 widowsxp)

    i have a form on workbook "B" which opens workbook "A" which is protected with a password. i would like to open workbook "A" transfere the last cell in colum "A" to cell "A1" in Workbook "B". the value of cell "A1" + 1 is then placed in cell "A2" and the row (2:2) copied back to last cell in colum "A" +1 in workbook "A".
    the workbook is then closed and the password re-instated. i am using worksheet 1 in both workbooks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: worksheet unprotect (office2002 widowsxp)

    Does this do what you want

    <pre>Option Explicit
    Sub AlexanderDCode()
    Dim wkbA As Workbook
    Dim wksA As Worksheet
    Dim wksB As Worksheet

    Set wksB = ThisWorkbook.Worksheets("Sheet1")
    Set wkbA = Workbooks.Open("c:Workbook A.xls")
    Set wksA = wkbA.Worksheets("Sheet1")

    wksA.Unprotect ("ABC")
    With wksB
    .Range("A1") = wksA.Range("A65536").End(xlUp)
    .Range("a2") = .Range("A1") + 1
    .Rows("2:2").Copy _
    wksA.Range("A65536").End(xlUp).Offset(1, 0)
    End With
    wksA.Protect ("ABC")
    wkbA.Close (True)
    Set wkbA = Nothing
    Set wksA = Nothing
    Set wksB = Nothing
    End Sub</pre>


    I assumed the code is in workbookB. Be sure to change workbook name and path and worksheet names as appropriate.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: worksheet unprotect (office2002 widowsxp)

    this looks great and after perservering i have arrive at a different way.
    i look foirward to comparing the two different ways , i wil reply in full tomorrow.

    thank you for your help

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

    Re: worksheet unprotect (office2002 widowsxp)

    Hi Alexanderd,
    If your way works too, would you mind sharing it on the board with Steve's (<!profile=sdckapr>sdckapr<!/profile>) solution. Its always a bonus to get different methods posted as replies. One solution may be valuable to one lounger, and the other method be more appropriate for another lounger!
    Thanx
    Regards,
    Rudi

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: worksheet unprotect (office2002 widowsxp)

    Superfluous empty lines removed by HansV

    as you will see my way is with the two files in the same folder which works A OK for me, may be my pears could inprove on it
    ************************************************** ************************************************** ************************************
    Private Sub ABCDEF()
    Dim Wkb As Workbook
    Dim wksheet As Worksheet
    Dim Path As String
    Dim FName As String
    Dim Pass As String
    Dim Str As String
    Dim LastRow As Long
    Sheets("Sheet1").Select
    Path = ThisWorkbook.Path
    FName = "A.xls"
    Pass = "ABC"
    Sheet1.Unprotect Password:="ABC"
    Set Wkb = Workbooks.Open(Filename:=Path & "" & FName, Password:=Path)
    If ActiveSheet.ProtectContents = True Then
    ActiveSheet.Unprotect "ABC"
    LastRow = Wkb.Sheets(1).Range("A65536").End(xlUp).Row
    Windows("3aSparePartsrequestAok.xls").Activate
    Wkb.Sheets(1).Range("A" & LastRow).Copy Destination:=Range("A2") '??Paste Location Here??
    ThisWorkbook.Sheets(1).Range("A2").EntireRow.Copy Destination:=Wkb.Sheets(1).Range("A" & LastRow + 1)
    Windows("3aSparePartsrequestAok.xls").Activate
    Range("A2").Select
    Range("A3").Value = Range("A2").Value + 1
    Range("A2:A3").Select
    Selection.NumberFormat = "0"
    Range("A3").Select
    Rows("3:3").Select
    Selection.Copy
    Windows("A.xls").Activate
    Range("A65536").End(xlUp).Select
    ActiveSheet.Paste
    Range("A2").Select
    End If
    Application.DisplayAlerts = False
    ActiveSheet.Protect "ABC"
    ActiveWorkbook.Save
    Wkb.Close
    Application.DisplayAlerts = True
    Windows("3aSparePartsrequestAok.xls").Activate
    End Sub
    ************************************************** ****************************
    for all who answered my call for help THANK YOU. if my my method is of use to any one be my guest

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

    Re: worksheet unprotect (office2002 widowsxp)

    Bananas are far more popular these days than pears... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Added - on the other hand, <post#=498067>post 498067</post#>

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

    Re: worksheet unprotect (office2002 widowsxp)

    Tx Alexanderd!
    Regards,
    Rudi

Posting Permissions

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