worksheet unprotect (office2002 widowsxp)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

Does this do what you want

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

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

Steve

Reply | Quote

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

Reply | Quote

Hi Alexanderd,
If your way works too, would you mind sharing it on the board with Steve’s (sdckapr) 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

Reply | Quote

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

Reply | Quote

Bananas are far more popular these days than pears…

Added – on the other hand, post 498067

Reply | Quote

Tx Alexanderd!

Reply | Quote