Resize table using VBA

DavidG 0 Reputation points
2024-05-23T17:14:23.4833333+00:00

Hello,

I currently use the following code in order to copy data to another worksheet.

User's image

However, when I eliminate rows on the original worksheet, the range of the table for the second worksheet remains the same when I run the macro.

I was wondering if someone could help me understand which function or code I could use so that the table size on the second worksheet is updated to the size of the range whenever I run the macro the way it is.

Thanks :)

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,947 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,963 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,725 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Q&A Assist
    2024-05-23T17:14:23.51+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    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:

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.