Automatically enter month in a cell

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,267
Hi,
I get subscript out of range.
This is shown in yellow

Code:
With Sheets(arr(i))
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,267
My mistake,
Your pointer was correct then i looked and found a space.

Just thinking down th rd.
Should i wish to use this code for say multiple EXPENSES sheets, as opposed to writing the same thing say 6 times can you advise how to write it so its like,

Code:
arr = Array("INCOME (1)", "EXPENSES (1) [COLOR=#ff0000][B]THOUGH TO[/B][/COLOR] "EXPENSES (6)")
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,055
Office Version
365
Platform
Windows
For the time it takes to write that 6 times just add them to the array. You only need to do it once. Probably less typing that this reply :)
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,267
Thanks,was going to do that but thought about it wouldnt be clean.

Thanks for your time this afternoon
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,267
Morning,
Following on from the above my code now looks like this and works very well,thanks for your time yesterday.

Code:
Sub AddMonth()

Dim arr, i As Long


arr = Array("INCOME (1)", "INCOME (2)", "INCOME (3)", "EXPENSES (1)", "EXPENSES (2)", "EXPENSES (3)", "EXPENSES (4)", "EXPENSES (5)", "EXPENSES (6)", "EXPENSES (7)", "EXPENSES (8)")
For i = LBound(arr) To UBound(arr)
    With Sheets(arr(i))
        .Range("B1") = UCase(Format(Now, "mmmm"))
        .Range("B2") = Year(Now)
        With .Range("B1:B2")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            With .Font
                .Name = "Calibri"
                .FontStyle = "Bold"
                .Size = 11
            End With
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent1
                .TintAndShade = 0.799981688894314
                .PatternTintAndShade = 0
            End With
        End With
    End With
Next


End Sub
In fact i like it so much i wish to now add to it.
The worksheet in question is called MILEAGE.

I am not sure how / where to add it into the code above.
Ive followed your advice from yesterday regarding recording macro etc & cleaned it up the the code below.

I now need some help / advice on merging it into the above code.
Many thanks.

Code:
        .Range("B1:C1") = UCase(Format(Now, "mmmm"))        .Range("D1") = Year(Now)
    With .Range("B1:C1:D1")
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlCenter
    With .Font
         .Name = "Calibri"
         .FontStyle = "Bold"
         .Size = 24
    End With
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
   With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
Its basically the same as before apart from,
Cell Range
Font Size
Worksheet name

Have a nice day.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,055
Office Version
365
Platform
Windows
Just add this between the next and the end sub lines:

Code:
With Sheets("MILEAGE")
    .Range("B1:C1") = UCase(Format(Now, "mmmm"))
    .Range("D1") = Year(Now)
    With .Range("B1:D1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 24
        End With
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    End With
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,096,444
Messages
5,450,484
Members
405,613
Latest member
Arpit

This Week's Hot Topics

Top