Good Afternoon,

The reason that your error check causes you to always display an error is that it is placed in the wrong place in the sub. The messagebox and Exit Sub should happen AFTER the code tries to run. You can fix this by rearranging the sub (see code at the end of this post). As for the number limit, I suspect it has to do with the use of Integer vs Long. Integers max out at 32,767, while Long Integers max out at 2,147,483,647. When you are looking at comparing large spreadsheets, the bottom row may exceed the Integer limit, causing the issue. Do a find and replace in your module to replace all instances of "Integer" with "Long" and hopefully that will fix it.

Hope this helps,

Ben

Sub CommandButton1_Click()

Dim SHEET1 As String

SHEET1 = InputBox("Enter Sheet Name")
Dim SHEET2 As String

SHEET2 = InputBox("Enter Another Sheet Name")

On Error GoTo invalid
CompareWorksheets Worksheets(SHEET1), Worksheets(SHEET2)
Exit Sub

invalid:
MsgBox "One or both sheet names entered are invalid. Please re-enter."

End Sub