jlabrecque

New Member
Joined
Nov 7, 2018
Messages
14
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
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top