How to Fix Run-time error 9

[johnston]

I am trying to make a macro that creates a worksheet for each month of a year, with the user typing in the year. When I run the macro, I keep getting Run-time error 9 on this line of code about adjusting the column width:

Worksheets(MonthName(monthNum, True)).Range("A5:K5").ColumnWidth=15

 

[Fazza]

please google for "vba how to debug"

 

[Trebor76]

Month number 1(January).

Then you must not have a tab in the workbook exactly called Jan. If there is a tab called Jan check for leading or trailing spaces which will also cause the issue.

 

[johnston]

Hi Johnston

So therefore, if you are looping from numbers 1 to 12 and using that integer in your line of code, you must have worksheets called:

1
2
3
.
.
.
12


For example, if you place in the Immediate Window, or run it in a normal module, the following line:

Sheets("1").Activate

does a worksheet called "1" get activated, or does a run time error occur?

If it is the latter, you don't have a worksheet called "1".

Consequently, adapt your line of code to test, as a single line of code, in something like the following way:

Worksheets("[name of the worksheet you want to have the following range to have column width 15]").Range("A5:K5").ColumnWidth=15

Then once you know this works, then adapt your original line of code to match the name of the worksheet in your loop, or change the name of the worksheet to match "monthNum" in your code.

BTW, I'm not sure, if "monthNum" is an integer, why you have the Boolean "TRUE". Unless I'm missing something, this does not appear required, and is also probably interfering with the running of the line.

Cheers

pvr928

Works perfectly. Thank you!

 

[pvr928]

No worries - glad to hear it is now working for you.

To complement advice on this thread from other more experienced Excel users than myself, whenever you get an error, break the erroring line of code down into component parts to isolate the source of the error. In this case, it was because you were not referencing a worksheet correctly.

Cheers

pvr928