zakasnak
Active Member
- Joined
- Sep 21, 2005
- Messages
- 308
- Office Version
- 365
- 2019
- Platform
- Windows
- 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?
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:
Hitting debug highlights the line in red above. This code works correctly, except on this project.... could it be the number of sheets?
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?