Tables

[jlabrecque]

Hi,


I am having a slight problem with excel


I have two tables and two sheets.


Table 1 on Sheet 1
Table 2 on Sheet 2


The info on Table 1 feeds into Table 2 and is formatted for clients.


As I update table 1 it adds more rows at the bottom the rows do not get added onto table 2. So say the default table range is A1 to A5 any rows after A5 will not update onto the table on Sheet 2.


Can anyone provide some guidance on how to achieve this? I know you can go into the table on Sheet 2 and manually expand it by hitting tab in the bottom right cell, but I want it to update automatically. I also want to avoid having excess formulas as this can slow down/crash excel.

I have tried to VBA code below but it only works if you run the macro

Sub Test()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow


Set the_sheet = Sheets("Table")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add


table_object_row.Range(1, 1).Value = Sheets("A:A")


End Sub

Thank you for your ideas in advance

 

[Col Delane]

Without knowing the layout of your tables and the links into Table 1 from Table 2 it is a little difficult to provide a definitive answer.

Question:
How are you returning info from T1 to T2 - by direct formulas to cells in T1, or via some kind of look up formula (so that the position of T1 data can change but the data still be reflected correctly in T2)?

An idea to consider:

1. Create a a Defined Name (say TableRowCheck ) that RefersTo this formula [ = ROWS( Table1 ) - ROWS( Table2 ) ] rather than a range. The formula and therefore the Name will return a +/- integer value equal to the row delta.
2. If you use a formula in each table to create some kind of common key field (like an index No. for each data row in each table) you can use these to find and replicate the required data from T1 in T2 by using a lookup (like VLOOKUP or an Index/Match combo formula), and as these formula in T2 should autoexpand/replicate down the column as new rows are added to it (via your VBA)
3. You can then test that cell in your code with:

IF Range("TableRowCheck").value > 0 Then  '>> T1 has more rows than T2
 [your code to add Range("TableRowCheck").value rows to T2]
End If

​

The trick is to put your code inside an appropriate "Event handler" that will trigger when the relevant "event" occurs (e.g. Private Sub Worksheet_Calculate() added to the Sheet2 "Microsoft Excel Object" in the VBE Project Window will run every time Sheet2 is recalculated)

I hope that helps.