Macro to copy data monthly

[Aandyw42]

I have a worksheet called 'Data' with 3 totals on, Green total (cell G26), Amber total (cell G27) and Red total (cell G28). These totals are fixed in these cells and auto update from elsewhere.

On the same worksheet I have a monthly total of each type, column Z being January, column A being February etc.

Totals for January are Z8 (green total) Z9 (Amber total) and Z10 is (Red total).

What I need is a macro that copies the values in cells G26, G27 & G28 into the next monthly column on the first day of every month, this shows me how the totals change over the year.
Totals for February will be copied into A8, A9 & A10, march in B8, B9 & B10 etc.

This is the last piece I need to complete a large project, so any help would be much appreciated.

Thanks!

 

[Sektor]

Sub MontlyReport()
    Range("G26:G28").Copy IIf(IsEmpty(Range("Z8")), Range("Z8"), Cells(8, Cells(8, Columns.Count).End(xlToLeft).Column).Offset(0, 1))
End Sub

 

[Aandyw42]

Hi, thanks for the quick reply, Ive inserted a module into my data sheet and assigned the macro to a button, is this correct or does the macro run itself? I need it to run automatically if possible.

When I press the button it comes up with 'ref#' error in all the boxes, Im not sure what is happening.

 

[Sektor]

What "boxes" you're talking about? You asked to copy cells to other place.
Place this code into Workbook_Open event handler of ThisWorkbook module.

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Workbook_Open()

    [COLOR="Blue"]If[/COLOR] Day(Now) = 1 [COLOR="Blue"]Then[/COLOR]
        Range("G26:G28").Copy IIf(IsEmpty(Range("Z8")), Range("Z8"), Cells(8, Cells(8, Columns.Count).[COLOR="Blue"]End[/COLOR](xlToLeft).Column).Offset(0, 1))
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]