Automatically enter month in a cell

[ipbr21054]

Morning,

I use the same worksheet template every month.
I would like to put a code etc in Cell B1 which would just enter the month.
So say starting in July the cell is blank.
I open the worksheet the code looks at the current month from pc etc and enters JULY into Cell B1

Next month i would use the same blank template where the above happens again but AUGUST is the entered into Cell B1


I tried this but it just returned an error.

=TEXT(B1, "mmm")

Thanks

 

[ipbr21054]

Hi,
I get subscript out of range.
This is shown in yellow

With Sheets(arr(i))

 

[steve the fish]

Then either INCOME(1) or EXPENSES(1) sheet doesnt exist.

 

[ipbr21054]

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,

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

 

[steve the fish]

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]

Thanks,was going to do that but thought about it wouldnt be clean.

Thanks for your time this afternoon

 

[ipbr21054]

Morning,
Following on from the above my code now looks like this and works very well,thanks for your time yesterday.

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.

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

Just add this between the next and the end sub lines:

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

 

[ipbr21054]

Spot on many thanks.