CODEWITHSUNDEEP
excelviewexcel-2010
Victor Zakharov
Victor Zakharov

Reputation: 26424

Create an Excel view table?

I have the following raw table structure - a list of credit card transactions:

Date | Description | Debits | Credits

Date will be converted using this approach:

Other columns will stay AS IS. I can create the first row of a target table, but I need to expand it downwards to match the number of rows in the source table.

Is there a way to automate the last part, i.e. row expansion?

I am expecting the raw data table to grow over time, so the target table needs to adjust its row count as well (either fully automated or via a single-click macro).

There is a similar question on StackOverflow, but I am using named tables and named columns:

Table names:

Source : TableRawData
Target : ProcessedData

Date conversion formula I am using in the 1st column:

=MorphDate(TableRawData[@Date])

Upvotes: 0

Views: 299

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

Paste this code into the worksheet with the source Table (ListObject). I used the Table names you specified. You'll need to adjust the worksheet names to the actual ones in your workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim loSource As Excel.ListObject
Dim loTarget As Excel.ListObject
Dim wb As Excel.Workbook

Set wb = ThisWorkbook
With wb
    Set loSource = .Worksheets("Source").ListObjects("TableRawData")
    Set loTarget = .Worksheets("Target").ListObjects("ProcessedData")
    'Only change Target if Source has more rows or columns, i.e,
    'don't shrink, only grow.
    If loTarget.Range.Rows.Count < loSource.Range.Rows.Count Or _
        loTarget.Range.Columns.Count < loSource.Range.Columns.Count Then
        With loTarget
             'two kinds of 'Resize in one line!
            .Resize (.Range.Cells(1).Resize(loSource.Range.Rows.Count, loSource.Range.Columns.Count))
        End With
    End If
End With
End Sub

As noted in the comments this triggers if either the number of rows or columns gets bigger. If you want it to change if Target grows or shrinks then change the <'s to <>'s.

If you are interested in the two different Resizes used in the code, and some information on copying Tables, see my this post of mine.

Upvotes: 1

Related Questions