Automatically enter month in a cell

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
=TEXT(B1, "mmm")

Thanks
 
Last edited:
Hi,
I get subscript out of range.
This is shown in yellow

Code:
With Sheets(arr(i))
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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)")
 
Upvote 0
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 :)
 
Upvote 0
Thanks,was going to do that but thought about it wouldnt be clean.

Thanks for your time this afternoon
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top