Workbook comparison results in subscript out of range error

[whatisexcel]

Hi,

I'm using excel to compare two files, test.xls (generated) and base.xl
(my standard template against which all my test files will be compare
against). I have found a script a
http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.htm
which compares two workbooks.

I went and copied both subroutines into base.xls and modified th
TestCompreWorksheets() to

Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
' CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("test.xls").Worksheets("Sheet1")
End Sub

and everything worked, i.e. it told me when and where the cells didn'
match up right.

Then I created another two identical excel files, test2.xls (which i
the equivalent to my base.xls) and test2a.xls (which is equivalent t
my generated test result), and I copied and pasted the same thing int
test2.xls, and changed TestCompareWorksheets to

Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
' CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("test2a.xls").Worksheets("Sheet1")
End Sub

and now I get "run-time error '9': Subscript out of range" Am I missin
something here? N.B. I'm a first time user of Excel scripts but though
that this post would be more approapriate in the programming sectio
rather than the new user section

 

[Charles]

whatisexcel,

In your test2 did you want

Workbooks("test2a.xls").Worksheets("Sheet1")

or

Workbooks("test2.xls").Worksheets("Sheet1")


Charle

 

[whatisexcel]

I want test2 to be tested against test2a, so I'm guessing it's
Workbooks("test2a.xls").Worksheets("Sheet1")