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

 

[Zenru]

Try creating the worksheet first, then do whatever formatting you need to do.

 

[johnston]

Try creating the worksheet first, then do whatever formatting you need to do.

It can already create the worksheet. It just stops on the column width code.

 

[Zenru]

Well, the error is about the worksheet not existing. Can you post the rest of the code? Where you create the worksheet most importantly.

 

[johnston]

Well, the error is about the worksheet not existing. Can you post the rest of the code? Where you create the worksheet most importantly.

It's a lot of lines. How do post all of it because when I try to paste it, nothing happens.

 

[Trebor76]

What value are you assigning to the monthNum variable? If for example it's 1 you must have a tab in the workbook exactly called Jan

 

[johnston]

What value are you assigning to the monthNum variable? If for example it's 1 you must have a tab in the workbook exactly called Jan

I'm using a For Loop. This will go through all 12 months. So I have:
For monthNum = 1 to 12

code

Next monthNum

 

[Trebor76]

Which number is it getting stuck on?

On a separate note individual tabs that are identical in layout are rarely needed (just my humble opinion).

 

[johnston]

Which number is it getting stuck on?

On a separate note individual tabs that are identical in layout are rarely needed (just my humble opinion).

Month number 1(January).

 

[pvr928]

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