How to summarize dates on a cover sheet

Space250 

You can do it with VBA.

Here's an example VBA program:

1. Open your Excel file and press ALT+F11 to open the Visual Basic Editor.

2. In the Visual Basic Editor, go to Insert -> Module.

3. In the new module, copy and paste the following code:

Sub UpdateCoverSheet()

    Dim CoverSheet As Worksheet
    Dim DataSheet1 As Worksheet
    Dim DataSheet2 As Worksheet
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim i As Long
    Dim j As Long
    Dim ClientName As String
    Dim UpcomingDate As Date
    Dim NextWeek As Date
    
    Set CoverSheet = ThisWorkbook.Sheets("Cover Sheet")
    Set DataSheet1 = ThisWorkbook.Sheets("Sheet1")
    Set DataSheet2 = ThisWorkbook.Sheets("Sheet2")
    LastRow1 = DataSheet1.Cells(Rows.Count, "A").End(xlUp).Row
    LastRow2 = DataSheet2.Cells(Rows.Count, "A").End(xlUp).Row
    NextWeek = Date + 7
    
    'Clear existing data in Cover Sheet
    CoverSheet.Range("A2:B" & CoverSheet.Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
    
    'Loop through DataSheet1 and add data to Cover Sheet
    For i = 2 To LastRow1
        ClientName = DataSheet1.Cells(i, 1).Value
        UpcomingDate = DataSheet1.Cells(i, 2).Value
        If ClientName <> "" And UpcomingDate >= Date And UpcomingDate <= NextWeek Then
            j = CoverSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
            CoverSheet.Cells(j, "A").Value = ClientName
            CoverSheet.Cells(j, "B").Value = UpcomingDate
        End If
    Next i
    
    'Loop through DataSheet2 and add data to Cover Sheet
    For i = 2 To LastRow2
        ClientName = DataSheet2.Cells(i, 1).Value
        UpcomingDate = DataSheet2.Cells(i, 2).Value
        If ClientName <> "" And UpcomingDate >= Date And UpcomingDate <= NextWeek Then
            j = CoverSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
            CoverSheet.Cells(j, "A").Value = ClientName
            CoverSheet.Cells(j, "B").Value = UpcomingDate
        End If
    Next i
    
End Sub

1. Change the sheet names "Cover Sheet," "Sheet1," and "Sheet2" in the code to match your actual sheet names.

2. Save the module and close the Visual Basic Editor.

3. Go back to your Excel file and press ALT+F8 to open the Macro dialog box.

4. Select the "UpdateCoverSheet" macro and click the "Run" button.

5. The macro will loop through both Sheet1 and Sheet2, find all client names and upcoming dates that meet the criteria for "this week" or the next 7 days based on the current date, and add them to the Cover Sheet.

6. If you want to update the data on the Cover Sheet, simply run the macro again.

I hope this helps!