Run time error 9 : Subscript out of range
 

Hi All,

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

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

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 :confused:


--
JAtz_DA_WAY
------------------------------------------------------------------------
JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php...o&userid=26684
View this thread: http://www.excelforum.com/showthread...hreadid=399567

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"
wrote
in message
...

Hi All,

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

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

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 :confused:


--
JAtz_DA_WAY
------------------------------------------------------------------------
JAtz_DA_WAY's Profile:
http://www.excelforum.com/member.php...o&userid=26684
View this thread:
http://www.excelforum.com/showthread...hreadid=399567

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 the
different ones.

Thanks in advance...
Regards,

Jatz


--
JAtz_DA_WAY
------------------------------------------------------------------------
JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php...o&userid=26684
View this thread: http://www.excelforum.com/showthread...hreadid=399567

Any Answers Please ???


--
JAtz_DA_WAY
------------------------------------------------------------------------
JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php...o&userid=26684
View this thread: http://www.excelforum.com/showthread...hreadid=399567

And also in the same directory !!


--
JAtz_DA_WAY
------------------------------------------------------------------------
JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php...o&userid=26684
View this thread: http://www.excelforum.com/showthread...hreadid=399567

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 wrote:

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 the
different ones.

Thanks in advance...
Regards,

Jatz

--
JAtz_DA_WAY
------------------------------------------------------------------------
JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php...o&userid=26684
View this thread: http://www.excelforum.com/showthread...hreadid=399567

--

Dave Peterson

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

Regards,

Jatz


--
JAtz_DA_WAY
------------------------------------------------------------------------
JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php...o&userid=26684
View this thread: http://www.excelforum.com/showthread...hreadid=399567