Run-time error: '9'

B

Bishop

This code is giving me a Run-time error: '9' Subscript out of range:

Dim CDLastRow As Long 'Catalyst Dump
Dim EDLastRow As Long 'Exported Data

CDLastRow = Workbooks("Test Tally SheetII.xlsm").Worksheets _
("Catalyst Dump").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13

Per some previous advice I added the file type to "Test Tally SheetII.xlsm"
Since I'm working in 07 and my sheet is saved as .xlsm I used that instead of
..xls. But it locks up on this line still.
 
J

Jacob Skaria

This is to do with the workbook name only..Please check for spaces...OR in
immediate window type ?Activeworkbook.name and copy paste..

If it is the activeworkbook you can try the below



Dim CDLastRow As Long 'Catalyst Dump
Dim EDLastRow As Long 'Exported Data

CDLastRow = Activeworkbook.Sheets("Catalyst Dump").Cells(Rows.Count,
"A").End(xlUp).Row
Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13

If this post helps click Yes
 
D

Dave Peterson

First, there are two logical lines in your posted code that could cause the
error.
CDLastRow = Workbooks("Test Tally SheetII.xlsm").Worksheets _
("Catalyst Dump").Cells(Rows.Count, "A").End(xlUp).Row

This error means that either you don't have a workbook named
"test tally sheetii.xlsm" open.

Or that you have a workbook open with that name, but it doesn't contain a
worksheet named "catalyst dump"

If it locks up on that last line
Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13

then the activeworkbook doesn't have a worksheet named "catalyst dump".

You could qualify that worksheet like you did in top line:
Workbooks("Test Tally SheetII.xlsm").Worksheets("Catalyst Dump") _
.Columns("D").ColumnWidth = 13
 
J

Jacob Skaria

And also check for the exact sheet name "Catalyst Dump" (with no spaces)

If this post helps click Yes
 
B

Bishop

Ok, your "?Activeworkbook.name" in the Immediate Window trick gave me this:
Test Tally SheetII.xls
So I changed the file type from .xlsm to .xls and that worked. BUT it only
works if that is the active workbook. I'm trying to make this code work no
matter what sheet is active. Now I'm getting '1004' again.
 
B

Bishop

Ok, so I added the following line:
Workbooks("Test Tally SheetII.xls").Activate

And now it works as expected. Thanks for you help. You've answered several
of my questions and your advise is always solid.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top