Advice for existing code where todays month is entered to a cell

[ipbr21054]

Morning,

I am currently using this code supplied below but i overlooked something when i was applying the code etc.

The code takes the current month and enters it into cell B1 on several worksheets within the same workbook.
The part i overlooked was that on the 1st day of the month when i transfer all the sheets data by using the Copy To Summary Sheet button it obviously shows the current month & in this case "September" in cell B1 where all the values on the sheet are for the previous month "August"

So can you advise a work around or advise me please.

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


End Sub

 

[NoSparks]

Dates being put in can be made dependant upon when they're put in by replace the red

    With Sheets(arr(i))
        .Range("B1") = UCase(Format(Now, "mmmm"))
        .Range("B2") = Year(Now)
        With .Range("B1:B2")

with this

        If Month(Date) = 1 And Day(Date) < 8 Then   'first week of January, ie: a new year
            .Range("B1") = UCase(Format(DateAdd("m", -1, Date), "mmmm"))
            .Range("B2") = Year(Date) - 1
        ElseIf Day(Date) < 8 Then   'first week of any other month
            .Range("B1") = UCase(Format(DateAdd("m", -1, Date), "mmmm"))
            .Range("B2") = Year(Date)
        Else    'before end of any month
            .Range("B1") = UCase(Format(Date, "mmmm"))
            .Range("B2") = Year(Date)
        End If

 

[ipbr21054]

Hi,
Can you advise what or how that will work.
Ive now changed it but it still shows September.

 

[NoSparks]

The comments in the code tell you when the code is run what will be put into the cells.
You said on the first of the month but what if it wasn't til the 2nd or 3rd ?
Made the grace period a week to account for that.

Nothing in what you posted has to do with transferring sheet data,
no idea if this is for prior to or after transfer, but if you stop the code just ahead of With Sheets("MILEAGE")

    End With
Next
Stop
With Sheets("MILEAGE")
    .Range("B1:C1") = UCase(Format(Now, "mmmm"))

you'll be able to look at the sheets and see what the month and year are.

 

[ipbr21054]

Hi,
At the end of the month i need to transfer the value from certain cells to my summary sheet,this is done with the transfer button.
So then if i go look at the summary sheet i can see,
Income 10,000
Fuel £350
Postage £234
etc etc placed in cells next to the month in question.

I then do the same each month and the value are then put under the next month in question.

So i come across this on the weekend where when i looked at the summary sheet i had seen that August was blank & september had the values shown.

 

[NoSparks]

So, is the code you've posted even used ?

 

[ipbr21054]

Hi,
Looking at it most is styling.

I also use it for Current Month, Ucase & Year.

 

[NoSparks]

How and when do you run this macro and what is its purpose ?

 

[ipbr21054]

On the worksheets i have income and expenses values.
At the end of the month i press a transfer button which collects the values from the cells which are at the bottom of each income / expense column and pastes them to another worksheet which is my summary for that month in question.

I do this at the end of the month.
Example.
Expenses 3 sheet
Fees £20
Postage £44
Purchases £100

Income 2 sheet
Gross income £100
Fees £12

Mileage sheet
Fuel allowance
£10

So at the end of the month i press the transfer button and the values in certain cells as mentioned above are copied and then pasted in that months summary sheet.

I will then have summary sheets for each month of the year
Under each month will have the titles above and also the values

Inland Revenue purposes.
I can then see how much fuel used in 1 year etc etc

The macro i supplied add styles to the sheet along with date and year etc.

The transfer button has a code which copies / pastes the value.
I didnt show that here as i thought the code supplied is what you require.

If you need other info or the other code please advise and i will put it here tomorrow.

 

[NoSparks]

The suggestion of post 2 answers the question you asked.

I had hoped the answers to "How and when do you run this macro and what is its purpose" would have pertained to the code
rather than the Inland Revenue, your fuel use or what time of the month it is.
Something like:
How... called from within the sub initiated by the transfer button.
When... before-or-after ??? all the Invoice and Expense sheets data is transferred to "Summary"
What is its purpose... to prepare the existing Invoice and Expense sheets for the next month.