Run time error 9 : Subscript out of range

[JAtz_DA_WAY]

Hi All,

I was trying to execute the macro on the link:
http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

But I get the error: "Run time error 9 : Subscript out of range" when I
try to compare 2 workbooks.

The line where i get error is:

CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")

I even tried renaming the file on my system to workbookname.xls.....but
it just doeesnt work.

Please advise...

Regards,

Jatz

 

[Chip Pearson]

You'll get a run time error 9 when the specified item is not in
the collection. Are you sure the workbooks have sheets named
'Sheet1' and 'Sheet2'?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"JAtz_DA_WAY"
in message
news:[email protected]...

 

[JAtz_DA_WAY]

Thanks for the quick reply...

Yes. The sheets are names sheet1 & sheet2.

It works fine if i change the statement :
-CompareWorksheets ActiveWorkbook.Worksheets("Sheet1")
Workbooks("c1.xls").Worksheets("Sheet2")-
to

-'CompareWorksheets ActiveWorkbook.Worksheets("Sheet1")
ActiveWorkbook.Worksheets("Sheet2")-

But this way I will be comparing sheets in the same workbook & not th
different ones.

Thanks in advance...
Regards,

Jat

 

[JAtz_DA_WAY]

Any Answers Please ??

 

[JAtz_DA_WAY]

And also in the same directory !

 

[Dave Peterson]

This macro requires that c1.xls be open, too.

Is it?

And are you sure you have the correct name--it's c1.xls. That second character
is a 1 (one), not an L (ell).

And are you sure that there is a worksheet named Sheet1 in the activeworkbook
and a worksheet named Sheet2 in that c1.xls workbook?

 

[JAtz_DA_WAY]

Thanks Dave, After opening the file & executing the macro, it works.

Regards,

Jatz