如何用VBA动态自动填充目标区域？Table2更新代码故障排查

First, let's break down why your original code is failing:

• Over-reliance on Select and ActiveCell makes the macro fragile—any unintended user interaction during execution can throw it off.
• Direct Range references for structured tables don't leverage Excel's built-in table (ListObject) functionality, which handles dynamic rows/columns much more reliably.
• There's a likely typo in your code (you mention Table2 but reference Table3) — we'll correct that in the solution.

Here's a robust, error-resistant version of your macro that meets your requirements:

Sub Auto_Fill()
    Dim tbl1 As ListObject
    Dim tbl2 As ListObject
    Dim lastRowTbl1 As Range
    Dim lastDataRowTbl2 As Range
    Dim fillRange As Range
    
    ' Set references to your tables (change names if they're different in your workbook)
    Set tbl1 = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1") ' Replace "Sheet1" with your actual sheet name
    Set tbl2 = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table2")
    
    ' Get the last non-empty cell in Table1's "U/C LEVEL" column (C column)
    Set lastRowTbl1 = tbl1.ListColumns("U/C LEVEL").Range.Find( _
                        What:="*", _
                        LookIn:=xlValues, _
                        SearchDirection:=xlPrevious)
    
    ' Check if the last cell has a value
    If Not lastRowTbl1 Is Nothing Then
        ' Get the last data row in Table2 (excludes header)
        Set lastDataRowTbl2 = tbl2.ListRows(tbl2.ListRows.Count).Range
        
        ' Define the range to fill: current last row + the new row below
        Set fillRange = Union(lastDataRowTbl2, tbl2.ListRows.Add.Range)
        
        ' Perform the AutoFill from the last row to the new row
        lastDataRowTbl2.AutoFill Destination:=fillRange, Type:=xlFillDefault
    End If
End Sub

Key improvements explained: #

• ListObject references: Instead of using Range with table headers, we directly access the table objects. This automatically adapts if your table grows/shrinks or columns are repositioned.
• No Select/ActiveCell: We work directly with range objects, eliminating the risk of macro failure due to accidental cell selection.
• Reliable last row detection: Using Find with SearchDirection:=xlPrevious is the most robust way to find the last non-empty cell in a table column.
• Dynamic fill range: We add a new row to Table2 (using ListRows.Add) and define the fill range as the combination of the old last row and the new row, ensuring the AutoFill targets exactly the right area.

Quick notes to adapt this to your workbook: #

• Replace "Sheet1" with the actual name of the worksheet containing your tables.
• Double-check that "Table1" and "Table2" match the exact names of your tables in Excel (you can find this in the Table Design tab when the table is selected).

内容的提问来源于stack exchange，提问作者Chad Echicken