Match Sheets code error

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
308
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
I have a MatchSheets code that I got from this board:
http://www.mrexcel.com/forum/showthread.php?t=370741

I have run into an issue, but I'm wondering if it's because of the number of sheets I'm trying to match?

Code:
Sub MatchSheets()
    Dim openName As String
    Dim firstWB As String
    Dim secondWB As String
    Dim firstSheet As String
    Dim secondSheet As String
    Dim sa As String
    Dim x As Integer, i As Integer
 
    firstWB = ActiveWorkbook.name
    openName = Application.GetOpenFilename("Excel Workbook (*.xls), *.xls")
    Workbooks.Open (openName)
    secondWB = ActiveWorkbook.name
 
    Workbooks(firstWB).Activate
 
    For i = 1 To ActiveWorkbook.Sheets.Count
        firstSheet = Sheets(i).name
        Workbooks(secondWB).Activate
        For x = 1 To ActiveWorkbook.Sheets.Count
            secondSheet = Sheets(x).name
            If firstSheet = secondSheet Then
                Workbooks(firstWB).Activate
                Sheets(i).Select
                Range(Range("A2"), Range("A65536").End(xlUp)).EntireRow.Copy  'if there are headers to skip change "A1" to first Row with data i.e. "A2" or "A5"
                Workbooks(secondWB).Activate
                Sheets(x).Select
                Range("A65536").End(xlUp).Offset(1, 0).Select
                ActiveSheet.Paste
                Exit For
            ElseIf x = ActiveWorkbook.Sheets.Count And firstSheet <> secondSheet Then
                Workbooks(firstWB).Activate
                [COLOR=red][B]Sheets(i).Copy After:=Workbooks(secondWB).Sheets(x) 'this will copy unique sheets from the first Workbook to the end of the second Workbook[/B][/COLOR]
                Workbooks(secondWB).Activate
            End If
        Next
        Workbooks(firstWB).Activate
    Next
 
End Sub

I'm getting this error on the same sheet every time I run it.... maybe it's because of the sheet name, but I don't know:

Code:
Run-time error '-2147417848 (80010108)':
Automation error
The objectinvoked has disconnected from its clients

Hitting debug highlights the line in red above. This code works correctly, except on this project.... could it be the number of sheets?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,224,570
Messages
6,179,608
Members
452,930
Latest member
racefanjtd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top