Need Help with VBA for copying cell data from one sheet to another sheet in specific cells based on matching the Month and Year of Date

[pmbloom]

I have a workbook where a lot of employee data is updated monthly, screened for compliance, and reported. Since it is updated monthly the data obviously changes from month to month. However, I want to build a new sheet that tracks the trends from month to month, that is automated when the report month changes. Currently I have a form that pops up when the workbook is opened that allows the user to update the date (DD-MMM-YYYY format) that the report is being pulled for (i.e. the presentation date) and updates a specified cell that all conditional formatting for the rest of the report is based off. I want to also have VBA code that takes the aggregate data (i.e. # of employees in compliance or compliance rate) from one sheet and populate it to a sheet tracking trends every time the reporting month is changed. I want it displayed based on MMM-YY format. So in short If I change the reporting date, I want the workbook to create a record of the old data and place it on a table to be used in a chart on a separate sheet, before updating the new data. So referencing the screenshots below, I want to automate recording the data from Sheet 1 AE6:AE10 into Sheet 2 Columns B:F in the correct row matching the month and year of the presentation date on Sheet 1 A12 with the month-year date in Column A on Sheet 2.

 

[aaewalsh]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Check if the changed cell is the presentation date cell (Sheet 1 A12)
    If Target.Address = "$A$12" Then
        'Store the new value of the presentation date in a variable
        Dim newDate As Date
        newDate = Target.Value
        'Extract the month and year from the presentation date
        Dim month As Integer
        month = Month(newDate)
        Dim year As Integer
        year = Year(newDate)
        'Find the matching row on Sheet 2 based on the month and year
        Dim row As Integer
        row = FindRow(month, year)
        'Copy the data from Sheet 1 AE6:AE10
        Dim dataRange As Range
        Set dataRange = ThisWorkbook.Sheets("Sheet1").Range("AE6:AE10")
        'Paste the data on Sheet 2 in the matching row of Columns B:F
        ThisWorkbook.Sheets("Sheet2").Range("B" & row & ":F" & row).Value = dataRange.Value
    End If
End Sub

Private Function FindRow(ByVal month As Integer, ByVal year As Integer) As Integer
    'Loop through the first column of Sheet 2 (column A)
    For i = 1 To ThisWorkbook.Sheets("Sheet2").UsedRange.Rows.Count
        'Get the month and year from the current cell in column A
        Dim currentMonth As Integer
        currentMonth = Month(ThisWorkbook.Sheets("Sheet2").Cells(i, 1).Value)
        Dim currentYear As Integer
        currentYear = Year(ThisWorkbook.Sheets("Sheet2").Cells(i, 1).Value)
        'Compare the month and year with the input month and year
        If currentMonth = month And currentYear = year Then
            'If they match, return the current row number
            FindRow = i
            Exit Function
        End If
    Next i
End Function