VBA向现有ListObject复制数据遇错误，求原代码解决方案

Hey there, let's break down what's causing that error and fix it step by step.

Why the Original Error Happens #

When you delete all data rows from your ListObject using .Rows.Delete, the DataBodyRange property of the table becomes Nothing—it no longer exists. So when you try to call tbl.DataBodyRange.PasteSpecial, VBA throws an error because you're trying to access a property of an object that doesn't exist anymore.

Solution 1: Restore the DataBodyRange Before Pasting #

First, after deleting the existing data, check if the DataBodyRange is gone. If it is, add a blank row to the table to bring it back. Here's how to modify your code in the UpdateData sub:

With tbl
    ' Delete existing data (use DataBodyRange.Delete for precision)
    If Not .DataBodyRange Is Nothing Then
        .DataBodyRange.Delete
    End If
    ' Add a blank row if the table has no data rows left
    If .DataBodyRange Is Nothing Then
        .ListRows.Add
    End If
End With

' Now you can safely paste
NewData.Copy
tbl.DataBodyRange.PasteSpecial xlPasteValues
Application.CutCopyMode = False

Solution 2: Skip Copy-Paste Altogether (More Efficient) #

Copy-paste is slow and relies on the clipboard, which can cause unexpected issues. A better approach is to directly assign values from your source range to the table—this avoids clipboard dependencies and runs faster:

With tbl
    ' Delete existing data
    If Not .DataBodyRange Is Nothing Then
        .DataBodyRange.Delete
    End If
    
    ' Add enough rows to match the new data
    If NewData.Rows.Count > 0 Then
        ' If no rows exist, add the first one
        If .DataBodyRange Is Nothing Then
            .ListRows.Add
        End If
        ' Add additional rows if needed
        If NewData.Rows.Count > 1 Then
            .ListRows.Add Count:=NewData.Rows.Count - 1
        End If
        ' Assign values directly to the table
        .DataBodyRange.Resize(NewData.Rows.Count, NewData.Columns.Count).Value = NewData.Value
    End If
End With

Addressing the "Merged Cells" Error #

The 1004 error you saw when using Select and Activate is likely because your target ListObject (or its parent worksheet) has merged cells somewhere—even if your source data doesn't. Using object-based operations (like the solutions above) avoids needing to activate sheets or select cells, which is a core best practice in VBA.

Bonus: Optimize Your Loop Logic #

You're currently looping through all worksheets in ThisWorkbook for each sheet in the source workbook. A more efficient way is to check if the sheet exists directly, cutting down on unnecessary iterations:

' Replace your inner For Each loop with this
On Error Resume Next
Set Ws = ThisWorkbook.Worksheets(.Worksheets(i).Name)
On Error GoTo 0

If Not Ws Is Nothing Then
    ' Your existing table update logic goes here
    Set Tws = .Sheets(i)
    Set tbl = Ws.ListObjects(1)
    ' ... rest of your code ...
End If

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