Trying to loop through files and copy an array of sheets using VBA, getting Error 424 Object Required

Question

Whenever I make changes across standard files, I like to take two copies of the sheets I'm changing to (1) preserve the original values, and (2) compare to the changed sheet (Sheet1!A1 - Sheet2!A1) to make sure there are no unexpected differences. Once I'm comfortable with the updates, I then delete the duplicate sheets.

The code that loops through the files and makes the changes works fine, it's the code that copies the sheets that I can't figure out. Each file has two sheets, the sheet names are all different.

This is my code. It's the Sheets(Array(Sheet... lines that are giving me error code 424, object required.

Sub PleaseWork()
  Dim wb As Workbook
  For Each wb In Application.Workbooks
    If wb.Name <> "PERSONAL.XLSB" Then

        Debug.Print wb.Name
        wb.Activate
        
        Sheets(Array(Sheet1.Name, Sheet2.Name)).Copy Before:=Sheets(1) 
        Sheets(Array(Sheet1.Name, Sheet2.Name)).Copy Before:=Sheets(1)

        Sheets(1).UsedRange.SpecialCells(xlCellTypeFormulas, 23).FormulaR1C1 = _
           "=ROUND('" & Sheets(3).Name & "'!RC-'" & Sheets(5).Name & "'!RC,4)"
        Sheets(2).UsedRange.SpecialCells(xlCellTypeFormulas, 23).FormulaR1C1 = _
           "=ROUND('" & Sheets(4).Name & "'!RC-'" & Sheets(6).Name & "'!RC,4)"
    
        Sheet2.Activate
        Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("I16:I18,AD2:AG14").ClearContents
        Range("N3,N21,X3,X21,AC21,AC3").Value = "Cash Flows"
    End If
    Next wb
  MsgBox "All Finished!"    
End Sub

I've tried variations using ActiveWorkbook, ThisWorkbook, Application.ActiveWorkbook, Application.ThisWorkbook, etc. but nothing works. I'd really appreciate some help!

My code is stored in my Personal.xlsb workbook.

Thanks in advance.

Answer 1

In this bit, Sheets is expecting the name or index of a sheet. Sheet1.Name implies that you have an object named Sheet1 which you don't. Try changing it to Sheet(1).Name. Sheet(1) is an object representing the first sheet in the workbook and Sheet(1).Name is the name of that worksheet.

Same issue with Sheet2.

Your code

Sheets(Array(Sheet1.Name, Sheet2.Name)).Copy Before:=Sheets(1)

Should be

Sheets(Array(Sheets(1).Name, Sheets(2).Name)).Copy Before:=Sheets(1)

Your code will also fail on

Sheet2.Activate

Need to be

Sheets(2).Activate

Answer 2

Identify Sheets in Another Workbook by Their CodeName

• When you say

  the sheet names are all different,

  it's like one equation with two unknowns: unsolvable.

• Your code hints that the worksheets will be the first and the second, yet in the comments you say:

  but (1) some of the files might have more than two sheets (people added sheets after the file was created), and (2) the order of the extra sheets may vary (sometimes at the beginning, sometimes at the end). How can I reference Sheet1 and Sheet2 (the first sheets created in the file)?

• The only way to deal with this is by identifying the worksheets whose code names are Sheet1 and Sheet2, that is if nobody has messed with those ones.

• To do this, I've written two functions and implemented another variable (CopySheets), a collection of sheets (object), which will hold the worksheets.

• With StrComp using vbTextCompare, I'm allowing case-insensitivity i.e. Personal.xlsb vs PeRSonal.XLSB vs PERSONAL.XLSB.

• Setting the variable to Nothing ensures that if something goes wrong in the functions (a worksheet is not found), the sheets from the previous workbook will not be tested in the line If Not CopySheets Is Nothing Then.

• In the next line, the functions do the 'heavy lifting' which should yield in a sheet collection of the two worksheets.

• If Not CopySheets Is Nothing Then tests if it was successful.

• If CopySheets.Count = 2 Then additionally tests if both worksheets were found.

• The Debug.Print line will write the workbook names and their respective worksheet names to the Immediate window (Ctrl + G).

• This is where you continue with your code which is out of the scope of this post.

• Note that nothing has been changed nor activated. Only a reference has been created. Your workbook is in the same state as it was before.

Option Explicit

Sub PleaseWork()
    
    Dim wb As Workbook
    Dim CopySheets As Sheets
    
    Debug.Print vbLf & "Name", "1st", "2nd"
    
    For Each wb In Workbooks
        If StrComp(wb.Name, "PERSONAL.XLSB", vbTextCompare) <> 0 Then
            Set CopySheets = Nothing
            Set CopySheets = RefSheetsFromCodeNames(wb, "Sheet1", "Sheet2")
            If Not CopySheets Is Nothing Then
                If CopySheets.Count = 2 Then
                    Debug.Print wb.Name, CopySheets(1).Name, CopySheets(2).Name
                    
                    ' Try first without your code.
                    ' Check the results In the Immediate window ('Ctrl+G').
                    ' If acceptable, continue by adding your code, e.g.:
                    
                    'CopySheets.Copy Before:=Sheets(1)
                
                'Else ' only one sheet
                End If
            'Else ' sheets not identified (found)
            End If
        'Else ' it's 'PERSONAL.XLSB'
        End If
    Next wb
    
    MsgBox "All Finished!", vbInformation, "Please work!"
    
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a reference to a sheet collection identified
'               by the sheet code names.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefSheetsFromCodeNames( _
    ByVal wb As Workbook, _
    ParamArray CodeNames() As Variant) _
As Sheets
    Const ProcName As String = "RefSheetsFromCodeNames"
    'On Error GoTo ClearError

    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

    Dim CodeName As Variant
    Dim SheetName As String
    For Each CodeName In CodeNames
        SheetName = GetSheetNameFromCodeName(wb, CodeName)
        If Len(SheetName) > 0 Then
            dict(SheetName) = Empty
        End If
    Next CodeName
    
    If dict.Count = 0 Then Exit Function
    Set RefSheetsFromCodeNames = wb.Sheets(dict.Keys)
    
ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "': Unexpected Error!" & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "        " & Err.Description
    Resume ProcExit
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the sheet name of a sheet in a workbook ('wb')
'               identified by its code name ('CodeName').
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetSheetNameFromCodeName( _
    ByVal wb As Workbook, _
    ByVal CodeName As String) _
As String
    Const ProcName As String = "GetSheetNameFromCodeName"
    On Error GoTo ClearError

    GetSheetNameFromCodeName = wb.VBProject.VBComponents(CodeName) _
        .Properties("Name")

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "': Unexpected Error!" & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "        " & Err.Description
    Resume ProcExit
End Function