Unlock Workbook A from VBS in Workbook B

[ChetEllis]

I have two workbooks "Checkbook.xlsm" and "TaxReporter.xlsm". The Checkbook has all cells locked and uses VBA forms to accept input, verify information and insert rows. There are twelve monthly spreadsheets labeled "January" through "December" with named ranges named "Payroll_Jan" though "Payroll_Dec". The following code fails is the checkbook is locked but works if it is not locked.

VBA from TaxReporter.xlsm

goSourceWorkbook.Activate
goSourceWorkbook.Sheets(gsWorkbookSheetName).Select
ActiveCell.SpecialCells(xlLastCell).Select
lOldLastRow = ActiveCell.Row
sOldRange = "C6:C" + CStr(lOldLastRow)
goSourceWorkbook.Sheets(gsWorkbookSheetName).Range(sOldRange).Activate

In my VBA code in TaxReporter.xlsm I would like to unlock the Ccheckbook.xlsm extract the needed data and then re-lock the Checkbook.xlsm.

Does anyone have an idea how I can do this?




I am new to the forum and searched but did not find an answer. This is my first post so I apologize for any rules I might have broken.

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

The error occurs when trying to select a range on a worksheet with protection that doesn't allow selection of those cells.

You're correct that one workaround would be to use VBA to unprotect, copy-paste, then reprotect your sheet.
A better solution would be to copy and paste the desired range without Selecting it.

Here's an example. You'll need to modify the sheet and column references to match your setup.

Option Explicit

Dim goSourceWorkbookSheets As Object
Dim gsWorkbookSheetName As String

Sub CopyWithoutSelection()
 Dim lLastRowSource As Long
 Dim lLastRowDestination As Long
    
 Call InitializeGlobals
 
 With goSourceWorkbookSheets(gsWorkbookSheetName)
   lLastRowSource = .Cells(.Rows.Count, "C").End(xlUp).Row
   If lLastRowSource >= 6 Then
      '---copy onto clipboard
      .Range("C6:C" + CStr(lLastRowSource)).Copy
      
      '--ThisWorkbook is where this code is held (TaxReporter.xlsm)
      With ThisWorkbook.Sheets("Summary")
      
      '--find the last row of data in destination
         lLastRowDestination = .Cells(.Rows.Count, "A").End(xlUp).Row
         
         '--paste into cell below last row of data
         .Range("A" & CStr(lLastRowDestination + 1)) _
            .PasteSpecial (xlPasteValues) 'or xlPasteAll
      End With
   Else
      MsgBox "No data found to copy"
   End If
   
 End With
End Sub

Private Sub InitializeGlobals()
'--assigns values to global variables referenced by other procedures
   Set goSourceWorkbookSheets = Workbooks("Checkbook.xlsm").Worksheets
   gsWorkbookSheetName = "January"
End Sub

 

[ChetEllis]

Jerry;

I sincerely appreciate your reply. I have been using the "selected range" for a "Find" and will need to slightly rework my logic to copy the source data into a spare worksheet in my current workbook.

I look forward to being a more active member of the forum.

Have a great day.

Chet

 

[Jerry Sullivan]

Chet, I'm happy to hear that helped and look forward to your participation in the forum.