Sub AddMonthYearColumn()

Dim ws As Worksheet

Dim lastRow As Long

Dim dateColumn As String

Dim outputColumn As String

Dim outputColumnYear As String

Dim outputColumnC As String

' Define the worksheet

Set ws = ThisWorkbook.Sheets(1) ' Change the sheet index or name as needed

' Define the columns

outputColumnC = "C" ' The column for Country & Product

dateColumn = "G" ' Adjust to the actual column containing dates

outputColumn = "H" ' The column where the "Month-Year" data will be added

outputColumnYear = "I" ' The column where the "Year" data will be added

' Find the last row in the Date column

lastRow = ws.Cells(ws.Rows.Count, dateColumn).End(xlUp).Row

' Add header for the new column Month-Year

ws.Cells(3, outputColumn).Value = "Month-Year"

ws.Cells(3, outputColumn).Font.Bold = True

' Add header for the new column Year

ws.Cells(3, outputColumnYear).Value = "Year"

ws.Cells(3, outputColumnYear).Font.Bold = True

' Loop through the rows to extract Month-Year and Year

Dim i As Long

For i = 4 To lastRow

If IsDate(ws.Cells(i, dateColumn).Value) Then

ws.Cells(i, outputColumn).Value = Format(ws.Cells(i, dateColumn).Value, "mmmm-yyyy")

ws.Cells(i, outputColumnYear).Value = Format(ws.Cells(i, dateColumn).Value, "yyyy")

Else

ws.Cells(i, outputColumn).Value = "Invalid Date"

ws.Cells(i, outputColumnYear).Value = "Invalid Date"

End If

Next i

' Clear any existing filters

If ws.AutoFilterMode Then

ws.AutoFilterMode = False

End If

' Apply filter to the entire range

Dim lastColumn As Long

lastColumn = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column

ws.Range(ws.Cells(3, 1), ws.Cells(lastRow, lastColumn)).AutoFilter

' Autofit new columns for readability

ws.Columns(outputColumn).AutoFit

ws.Columns(outputColumnYear).AutoFit

MsgBox "Month-Year and Year columns added and filter applied successfully!", vbInformation

End Sub