Insert data to the right of last days data

Could an expert help me out with this one please

I have attached a file that has sheet 1 and sheet 2. Think of sheet 1 as the starting point and sheet 2 after the code has bee run.

Each day the report is run and each day I need to add a new column to the right of the last day working days data. So on sheet 1 you will see there are 3 days 1/4, 2/4 and 3/4

So today I need VBA code that will insert today to the right of 3/4 and include all of the conditional formats that are in the cells.  I also need there to be 3 blank columns between the last day and the column headed 'Type'

Sheet 2 is what I need it to look like after the Macro has run.

I hope that explains it clearly.

Thanks in advance
Who is Participating?
Robberbaron (robr)Commented:
try this code.

note that it needs two named cells.  month_start and month_today.
'version 1.  robberbaron @ ee   7/May/2015
Sub AddToday()
    Dim wsA As Worksheet
    Set wsA = ActiveSheet
    If Day(Now()) = 1 Then
        'delete all the current cells
        Range(Range("month_start").Offset(0, 1), Range("month_today")).EntireColumn.Select
        Application.CutCopyMode = False
        'set new column & insert
        Range("month_start").EntireColumn.Offset(0, 2).Select
        Selection.Insert Shift:=xlToRight

        'save new today cell
        lastcell = "=" & Range("month_start").Address(ReferenceStyle:=xlR1C1, External:=True)
        'select the column to copy
        'set new column & insert
        Range("month_today").EntireColumn.Offset(0, 1).Select
        Selection.Insert Shift:=xlToRight
        Application.CutCopyMode = False
        'save new today cell
        lastcell = "=" & Range("month_today").Offset(0, 1).Address(ReferenceStyle:=xlR1C1, External:=True)
    End If

    wsA.Names.Add Name:="month_today", RefersToR1C1:=lastcell
    Range("month_today").Value = Int(Now())
    'ActiveCell.FormulaR1C1 = "5/7/2015"
End Sub

Open in new window

JagwarmanAuthor Commented:
An add on to the above info is:

1) I have uploaded a new file because the dates in the original could cause confusion so I have changed to dates in May as they originally showed April.

2) The file will be used for the entire month so when we move into a new month the first working day of the new month needs to start in column J and all other dates from previous month needs to be cleared.

Many thanks
JagwarmanAuthor Commented:
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Attached is an XLSM file with code in Module1 and a button to execute the code from the first sheet.
JagwarmanAuthor Commented:

I get Run-time error '1004' : Method 'Range' of object'_Global' failed at this line of code with your macro


is that because I have to put month_start and month_today inspecific cells?
Robberbaron (robr)Commented:
yes.  look at my example sheet.

name J3 as month_start.
name the last cell (eg K3/L3) as month_today.

they get reassigned as necessary by the macro,
JagwarmanAuthor Commented:
both excellent thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.