Run-time error '424': Object required for VBA copy/paste macro with unprotect workbook

Question

I have created a macro to copy/paste as I need. However, I recently realized I will need to unhide the sheet I need and unprotect my workbook with VBA. The entire workbook needs to be unprotected with the password, not just the one sheet. When I run my code with Workbook.Unprotect Password:="SOCKS", I get a run-time error '424'.

Sub CopyRange()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim i As Integer
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim strExtension As String
    Dim LastRow As Long
    Dim a As Integer

    Const strPath As String = "H:\My Documents\Timesheet Folder\" 'Specify file path here next to the = in the " "
    ChDir strPath
    strExtension = Dir(strPath & "*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource.Sheets("Data") 'Specify the name of the sheet here in the " " next to .Sheets
            a = .Cells(.Rows.Count, 1).End(xlUp).Row
            Worksheets("Data").Visible = True
            Workbook.Unprotect Password:="SOCKS" 'Here is the problem line
            For i = 1 To a
                If .Cells(i, 1).Value = "1934001" And .Cells(i, 2).Value = "GSMP_North_Haledon" And .Cells(i, 4).Value = "2019" And .Cells(i, 5).Value = "September" And .Cells(i, 6).Value = "20" And .Cells(i, 17).Value = "SRo" Then
                    'In the line below, name the sheet of the open destination workbook in the " " next to .Worksheets
                    LastRow = wkbDest.Worksheets("Data").Cells(wkbDest.Worksheets("Data").Rows.Count, "B").End(xlUp).Offset(1).Row
                    Worksheets("Data").Range(Worksheets("Data").Cells(i, 1), Worksheets("Data").Cells(i, 17)).Copy
                    wkbDest.Worksheets("Data").Range("B" & LastRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                End If
            Next
        End With
        wkbSource.Close savechanges:=False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Workbook by itself doesn't mean anything in your context, you haven't set it as a specific object. I think you mean to write wkbSource.Unprotect ... or because you already have it within a With clause, just write .Unprotect — jamheadart
@jamheadart The With block refers to the specific sheet Data, so .Unprotect will not work for the entire workbook. — riskypenguin
@M.Schalk Oh yeah, I didn't see that it had the extra objects attached.. — jamheadart

riskypenguin riskypenguin · Accepted Answer · 2019-09-30T12:23:32

2

votes

That line is not properly qualified, try this:

wkbSource.Unprotect Password:="SOCKS"

Run-time error '424': Object required for VBA copy/paste macro with unprotect workbook

2 Answers