Unprotect sheet/Workbook Excel VBA

Question

Sub VBA_Read_External_Workbook()

' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim sheet As String


' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Text files (*.xlsb),*.xlsb"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)
sheet.Unprotect ("CADDRP")

' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(3)


targetSheet.Range("A1", "C10").Value = sourceSheet.Range("D85", "D95").Value

' Close customer workbook
customerWorkbook.Close

End Sub

Hi guys, So I've manage to get this working but some of my client files are protected. So googling around I've manage to squeeze a line of sheet.unprotect code inside. But it gives me an error not "Object Required" Run time error '424'. I'm a newbie to excel VBA and would appreciate if can point me to the right direction. I'm guessing I miss out some variable declaration in the process?

Answer 1

Assuming you need to Unprotect the Sheet, not the Workbook.. Remove the sheet.Unprotect line where you have it currently and put it back in after setting the SourceSheet:

Set sourceSheet = customerWorkbook.Worksheets(3)
sourceSheet.Unprotect ("CADDRP")