Run-time error: '9'

[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.

 

[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

 

[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

 

[Jacob Skaria]

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

If this post helps click Yes

 

[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.

 

[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.