Resize table using VBA

To update the table size on the second worksheet to match the size of the range on the original worksheet, you can use the ListObject.Resize method in VBA. This method resizes the table to match the size of the range specified. Here is an example of how you can modify your code to include this method:

Sub CopyData()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim targetTable As ListObject
    
    ' Set the source range to copy from
    Set sourceRange = Worksheets("Sheet1").Range("A1:C10")
    
    ' Set the target range to copy to
    Set targetRange = Worksheets("Sheet2").Range("A1")
    
    ' Copy the data from the source range to the target range
    sourceRange.Copy Destination:=targetRange
    
    ' Resize the table on the second worksheet to match the size of the range
    Set targetTable = Worksheets("Sheet2").ListObjects("Table1")
    targetTable.Resize targetRange.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count)
End Sub

In this example, the ListObject.Resize method is used to resize the table named "Table1" on the second worksheet to match the size of the range copied from the first worksheet. The targetRange.Resize method is used to create a range of the same size as the source range, which is then passed to the ListObject.Resize method.

References:

• How to delete every other row on an Excel worksheet
• Methods for transferring data to Excel from Visual Basic