finding last column used and insert new data weekly

[broman5000]

Hey there,

I'm trying to find the last column and insert data after the last column used each week.

Every week will add on a column of data to the spreadsheet

I'm looking to add three more columns for the following below in row 14, 15 16 (see attachment) - starting in column DG and continue for each week

late
early
lunch

code will need to be in VBA of course. Please help if you can!

[SamT]

That doesn't fit the attachment. Can you rethink and restate the requirements, please?

Just guessing from looking at the attachment, but I would think that you want to extend Rows 3:8 one more column each week.

[broman5000]

Yes your right. My fault.
What you said in the 2nd paragraph is exactly the goal. Thx!

[SamT]

This code goes in Sheets("Level 2 Metrics w Volume")
Option Explicit

Sub AddWeeklyColumn()
Dim StartCell As Range

Set StartCell = Cells(6, Columns.Count).End(xlToLeft)
If StartCell = ThisWeekYearNumber Then Exit Sub

StartCell.Offset(-3, 1) = ThisWeekYearNumber
StartCell.Offset(-2).Resize(5, 2).FillRight

End Sub


Private Function ThisWeekYearNumber() As Double
Dim Tmp
Tmp = DateDiff("ww", "01/01", Date)

If Weekday("01/01") = 1 Then Tmp = Tmp + 1
If Len(Tmp) = 1 Then Tmp = "0" & Tmp

ThisWeekYearNumber = CDbl(Format(Date, "yyyy") & Tmp)
End Function

To fully automate the process, you can put this code in the ThisWorkbook Code Page
Private Sub Workbook_Open()
If Sheets("Level 2 Metrics w Volume").Cells(6, Columns.Count).End(xlToLeft) + 7 _
<= Date Then Sheets("Level 2 Metrics w Volume").AddWeeklyColumn
End Sub


Note that this requires the values and formulas in the @3 week's column, (C3:C7, in your attachment) The code assumes that all columns are up to date when used. It does not check to see if the columns were updated in the previous week. It will place the wrong ThisWeekYearNumber value in Row 3. Other Rows will be ok.