add dates until finishing the month based on current month

Tubrak · Mar 9, 2024

Hi
I search for macro to insert new column contains date(today) on the header after column C until finishing the month based on current month for each sheet name(current month).
should start after STOCK sheet from JAN sheet to DEC sheet. every sheet contains MONTH name depends on current month . so should add date(today) every day until finishing the month based on current month . with considering don't repeat inserting column if I added new column for day no need adding again for the same day.
I put the result for JAN,FEB,MAR after column C .
also cross posted here
https://www.excelforum.com/excel-pr...month-based-on-current-month.html#post5928144

Monty · Mar 9, 2024

Hello

You can use Google Apps Script in Google Sheets. Below is a script that should accomplish what you described:...try and let me know

Code:

function insertDateColumn() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var today = new Date();
  var currentMonth = today.getMonth();
 
  var months = ['JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'];
 
  for (var i = 0; i < months.length; i++) {
    if (i < currentMonth) {
      continue; // Skip past months
    }
    var month = months[i];
    var sheet = findSheetByName(sheets, month);
    if (sheet) {
      var lastColumn = sheet.getLastColumn();
      var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
      var columnIndex = headers.indexOf(month) + 4; // Start after column C
      var currentDate = new Date(today.getFullYear(), today.getMonth(), 1);
      var todayIndex = 0;
      
      while (currentDate.getMonth() === today.getMonth()) {
        var dateString = Utilities.formatDate(currentDate, Session.getScriptTimeZone(), 'MM/dd/yyyy');
        if (headers.indexOf(dateString) === -1) {
          sheet.insertColumnBefore(columnIndex + todayIndex);
          sheet.getRange(1, columnIndex + todayIndex).setValue(dateString);
        }
        currentDate.setDate(currentDate.getDate() + 1);
        todayIndex++;
      }
    }
  }
}

function findSheetByName(sheets, name) {
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getName() === name) {
      return sheets[i];
    }
  }
  return null;
}

Tubrak · Mar 9, 2024

Hi,
I don't know how use your suggetion !
doesn't seem to be vb excel .

Marc L · Mar 9, 2024

Monty,

why should I remind you this is very not a Google Sheets forum ?‼

Monty · Mar 10, 2024

Sorry Marc....Noted!

Marc L · Mar 11, 2024

Anyway, if you wrote the post #2 code, you should easily create some procedure just using some Excel & VBA basics …

Monty · Mar 11, 2024

This code performs thensame task using VBA

Code:

Sub InsertDateColumn()
    Dim ws As Worksheet
    Dim today As Date
    Dim currentMonth As Integer
    Dim months As Variant
    Dim i As Integer
    Dim month As String
    Dim lastColumn As Integer
    Dim headers As Variant
    Dim columnIndex As Integer
    Dim currentDate As Date
    Dim todayIndex As Integer
    Dim dateString As String
    
    Set ws = ThisWorkbook.ActiveSheet
    today = Date
    currentMonth = Month(today)
    months = Array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")
    
    For i = LBound(months) To UBound(months)
        If i < currentMonth - 1 Then
            GoTo NextMonth ' Skip past months
        End If
        
        month = months(i)
        Set ws = FindSheetByName(month)
        
        If Not ws Is Nothing Then
            lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
            headers = ws.Range(ws.Cells(1, 1), ws.Cells(1, lastColumn)).Value
            columnIndex = Application.Match(month, headers, 0) + 4 ' Start after column C
            currentDate = DateSerial(Year(today), Month(today), 1)
            todayIndex = 0
            
            Do While Month(currentDate) = Month(today)
                dateString = Format(currentDate, "MM/dd/yyyy")
                If IsError(Application.Match(dateString, headers, 0)) Then
                    ws.Columns(columnIndex + todayIndex).Insert Shift:=xlToRight
                    ws.Cells(1, columnIndex + todayIndex).Value = dateString
                End If
                currentDate = currentDate + 1
                todayIndex = todayIndex + 1
            Loop
        End If
        
NextMonth:
    Next i
End Sub

Function FindSheetByName(name As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = name Then
            Set FindSheetByName = ws
            Exit Function
        End If
    Next ws
    Set FindSheetByName = Nothing
End Function

Tubrak · Mar 11, 2024

Thanks Monty for comeback for me .
I tested and gives error as in picture !

Monty · Mar 11, 2024

I think there is date format issue, however modified code for you

Code:

Sub InsertDateColumn()
    Dim ws As Worksheet
    Dim todayDate As Date
    Dim currentMonth As Integer
    Dim months As Variant
    Dim i As Integer
    Dim month As String
    Dim lastColumn As Integer
    Dim headers As Variant
    Dim columnIndex As Integer
    Dim currentDate As Date
    Dim todayIndex As Integer
    Dim dateString As String
    
    Set ws = ThisWorkbook.ActiveSheet
    todayDate = Date
    currentMonth = Month(todayDate)
    months = Array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")
    
    For i = LBound(months) To UBound(months)
        If i < currentMonth - 1 Then GoTo NextMonth ' Skip past months
        month = months(i)
        Set ws = FindSheetByName(month)
        
        If Not ws Is Nothing Then
            lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
            headers = ws.Range(ws.Cells(1, 1), ws.Cells(1, lastColumn)).Value
            columnIndex = Application.Match(month, headers, 0) + 4 ' Start after column C
            currentDate = DateSerial(Year(todayDate), Month(todayDate), 1)
            todayIndex = 0
            
            Do While Month(currentDate) = Month(todayDate)
                dateString = Format(currentDate, "MM/dd/yyyy")
                
                If IsError(Application.Match(dateString, headers, 0)) Then
                    ws.Columns(columnIndex + todayIndex).Insert Shift:=xlToRight
                    ws.Cells(1, columnIndex + todayIndex).Value = dateString
                End If
                
                currentDate = currentDate + 1
                todayIndex = todayIndex + 1
            Loop
        End If
NextMonth:
    Next i
End Sub

Function FindSheetByName(name As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = name Then
            Set FindSheetByName = ws
            Exit Function
        End If
    Next ws
    Set FindSheetByName = Nothing
End Function

Tubrak · Mar 11, 2024

still the same error .
the problem seem from the decleration as I think .
so I expect the error should be from this line


                dateString = Format(currentDate, "MM/dd/yyyy")

if the problem date form .

Monty · Mar 11, 2024

Replace This

Code:

dateString = Format(currentDate, "MM/dd/yyyy")

With This

Code:

dateString = Format(currentDate, "mm/dd/yyyy")

Monty · Mar 11, 2024

Monty said:
Replace This

Code:

dateString = Format(currentDate, "MM/dd/yyyy")

With This

Code:

dateString = Format(currentDate, "mm/dd/yyyy")

This change should ensure proper formatting and hopefully resolve the "expected array" error. Let me know if it works!

Tubrak · Mar 11, 2024

can you tell me what's the difference?
the same thing as you posted in the code.I don't see any different for that line !
surely still the same error .

Marc L · Mar 11, 2024

Hi, does each STOCK sheet contain at least the first date within cell D1 ?

If yes then according to Excel basics that should need few codelines if :

'insert' means in fact adding the missing dates as according to your attachment there is nothing to 'insert' !
'current month' does very not mean the current month (aka March) but the month according to the worksheet.

Tubrak · Mar 12, 2024

Hi Marc

Hi, does each STOCK sheet contain at least the first date within cell D1 ?

I expected the macro create it, not from me

according to your attachment there is nothing to 'insert' !

no, just I added the dates as expected result for each sheet after column C to see how add dates for each sheet based on current month .

'current month' does very not mean the current month (aka March) but the month according to the worksheet.

I suppose the macro will match part of sheet name when add the dates for current month .
for instance STOCK_MAR will match with MAR word based on current month when add dates. anyway I don't mind if there is another way to match it ,

Marc L · Mar 12, 2024

Still confusing, not what you explained in your initial post …

As the current month is March, the VBA procedure must update only STOCK_MAR worksheet, that's it ?

Tubrak · Mar 12, 2024

As the current month is March, the VBA procedure must update only STOCK_MAR worksheet, that's it ?

based on current month yes,
as to JAN,FEB when the current month is Jan or FEB . the year will start from JAN to DEC after STOCK sheet
each sheet depends on current month.

Marc L · Mar 12, 2024

Ok, so Monty can you give it a try for only the current month, just creating the dates headers ?

Tubrak · Mar 15, 2024

Doesn't seem to be possible to achieve that ?!!

Tubrak · Mar 18, 2024

after many bumps especially the other forum and here it solved by jindon in the other forum . for the members or readers interest here is the code.

Code:

Sub test1()

    Dim i As Long, ii As Long, m, ws As Worksheet

    Dim col As Long, x(1 To 3), s As String, d As Long

    m = Split("JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT.NOV.DEC", ",")

    Set ws = Sheets("STOCK")

    For i = 1 To 3

        x(i) = ws.Columns(i).ColumnWidth

    Next

    For i = 1 To Month(Date)

        If Not Evaluate("isref('STOCK_" & m(i - 1) & "'!a1)") Then

            If i = 1 Then s = "STOCK" Else s = "STOCK_" & m(i - 2)

            Sheets.Add(, Sheets(s)).Name = "STOCK_" & m(i - 1)

            Sheets("stock").Columns("a:c").Copy Sheets("stock_" & m(i - 1)).[a1]

        End If

        If i = Month(Date) Then

            d = Day(Date)

        Else

            d = Day(DateSerial(Year(Date), i + 1, 0))

        End If

        With Sheets("stock_" & m(i - 1))

            If .[a1].CurrentRegion.Columns.Count < d + 3 Then

                Sheets("stock").[d1].Copy .[d1].Resize(, d)

                With .[d1].Resize(, d)

                    .Formula = "=date(year(today())," & i & ",column(a1))"

                    .Value = .Value

                End With

            End If

        End With

        If i > 1 Then Set ws = Sheets("STOCK_" & m(i - 2))

        With ws.[a1].CurrentRegion

            col = .Parent.Evaluate("max(if(" & .Offset(1).Address & "<>"""",column(" & .Address & ")))")

            .Columns(col).Copy Sheets("STOCK_" & m(i - 1)).[c1]

        End With

        With Sheets("STOCK_" & m(i - 1))

            .[c1] = ws.[c1]

            .[a1].CurrentRegion.Borders.Weight = 2

            .Columns.AutoFit

            For ii = 1 To 3

                .Columns(ii).ColumnWidth = x(ii)

            Next

            .Rows.AutoFit

        End With

    Next

End Sub

to understand the code how works
will create sheets names(MONTH) based on current month ( start from JAN to DEC) and will copy the columns A,B and the last column from sheet to next sheet and insert the dates based on date(today) .
I hope this usefule .
thanks for every one try to help me here.

Marc L · Mar 18, 2024

As suspected and confirmed by the code your explanation does not match your need …

For your future threads whatever the Excel forum try at least to better explain what you expect for

with an attachment well reflecting the before state and the exact expected result as well.

Tubrak · Mar 18, 2024

As suspected and confirmed by the code your explanation does not match your need …

because I don't expect modifying jindon's original code
the original thread has ever solved , I asked to create sheets names(month) and copy specific columns from sheet to next based on current month.
now when create this thread I need to add column (date) until finish dates for each sheet based on current month .
so jindon decided adjusting the original code instead of write another macro, I think he's right.
just I want it to clarify why could make you confusing .

add dates until finishing the month based on current month

New Member

Attachments

Well-Known Member

New Member

Excel Ninja

Well-Known Member

Excel Ninja

Well-Known Member

New Member

Attachments

Well-Known Member

New Member

Well-Known Member

Well-Known Member

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

New Member

Excel Ninja

New Member