Adding new rows from source sheet to linked sheet

[CarlaHobson]

I have a workbook with multiple sheets, and each sheet contains a single table. Each table has the same set of column headers. I am trying to consolidate and link all the tables/sheets into a single "subsheet" in the same workbook--so that all the data appears in one place for easier viewing--and I'd like new rows that are added to the source sheets to automatically update in the subsheet. I've copied each table and pasted it as a link into the subsheet. The problem is that any new rows of data added to the source sheets don't appear in the subsheet. I tried using Power Pivot but that did not achieve the result I'm trying for. Can anyone explain how to do this?

[#2 · Accepted solution]

@CarlaHobson 

You should use Power Query. 

Select the first table and under the data tab, in the "get and transform data" area, select "from table/range".  This will load PQ where you can format if you'd like.  Select close and load drop down, then close and load to-then choose only create connection.  Do the same for the other table.  Then you need to go back to PQ, to do so, select get data, then launch PQ editor.  Select table1, and choose Append Queries drop down, then select append queries as new.  In the box that pops up, table1 will be selected for you, you have to choose table2 in the bottom half of the screen, then hit ok.  Your mergered table will appear, and now you'll select the close and load drop down arrow, and choose close and load to Table.

When you want new data to show up in the new table, refresh all and it'll show up.

[#3]

Thank you! This was the perfect solution! Append queries!! :)

[#4]

For me, I don't want to use a power query because it crashes my PC of 4gb RAM yet I want to have very many 40 worksheets each having a table with more than 200 rows. I have a table1 linked to another table2 by formula. But the problem is that it doesn't add a new row when a record is created in table1.