Excel VBA 自动化工具：根据日期将 Sheet1 数据填充到 Sheet2 表格空白行

Sub FillData() Dim ws1 As Worksheet, ws2 As Worksheet Dim lastRow1 As Long, lastRow2 As Long Dim i As Long, j As Long, k As Long Dim currDate As Date, prevDate As Date Dim foundRow As Range

Set ws1 = ThisWorkbook.Sheets('Sheet1')
Set ws2 = ThisWorkbook.Sheets('Sheet2')

lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

' Loop through all rows in Sheet1
For i = 2 To lastRow1
    
    currDate = ws1.Cells(i, "A").Value
    
    ' If current date is different from previous date, find a blank row in Sheet2 to fill the data
    If currDate <> prevDate Then
    
        ' Loop through all tables in Sheet2
        For j = 1 To lastRow2 Step 10
            
            ' Check if the first cell in the table matches the current date
            Set foundRow = ws2.Range("A" & j).Find(What:=currDate, LookIn:=xlValues)
            
            ' If the date is found, check if there are enough blank rows to fill the data
            If Not foundRow Is Nothing Then
                k = foundRow.Row + 3
                
                If k + 7 > j + 9 Then ' Not enough blank rows in this table
                    ' Move to the next table
                Else ' Enough blank rows in this table
                    ' Fill the data in the blank rows
                    ws1.Range("B" & i & ":J" & i).Copy ws2.Range("B" & k & ":J" & (k + 6))
                    Exit For ' Stop searching for the next blank row
                End If
                
            End If
            
        Next j
        
    End If
    
    prevDate = currDate ' Update previous date
    
Next i

End Sub