Excel 宏自动填充数据到多表格：按日期分类并限制表格行数

Sub FillData() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim lastRow1 As Long Dim lastRow2 As Long Dim dateCol As Range Dim dateDict As Object Dim dateValue As Variant Dim i As Long Dim j As Long Dim k As Long

Set ws1 = ThisWorkbook.Sheets('Sheet1')
Set ws2 = ThisWorkbook.Sheets('Sheet2')
lastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
Set dateCol = ws1.Range("A2:A" & lastRow1)
Set dateDict = CreateObject("Scripting.Dictionary")

'将不同日期的行数据放入字典中
For i = 1 To dateCol.Cells.Count
    dateValue = dateCol.Cells(i).Value
    If Not dateDict.Exists(dateValue) Then
        dateDict.Add dateValue, ws1.Range("A" & i & ":A" & (i + 6)).Offset(, 1).Value
        i = i + 6
    End If
Next i

'填充到空白行中
For Each dateValue In dateDict.Keys
    For j = 4 To lastRow2 Step 10
        If WorksheetFunction.CountA(ws2.Range("A" & j & ":A" & (j + 6))) = 0 Then
            ws2.Range("A" & j & ":A" & (j + 6)).Value = dateDict(dateValue)
            Exit For
        End If
    Next j
Next dateValue

End Sub