Excel tables needs to auto expand when adding a new row

chris pike
chris pike used Ask the Experts™
on
Hi Experts
I have a simple Excel Table problem. I have 2 sheets and I want to mirror my table to a second sheet, but not all columns, and have the copy expand when I add new rows to the source.

I can copy links to the copy range (I tried table as well) but the copy will not expand when I add rows to the source.
Please see attached workbook with two sheets.
Thanks
Chris
dummy-sheet-tabe-to-range.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
It's a bad idea to duplicate data

You could use VBA code to add rows to both tables.
Rob HensonFinance Analyst

Commented:
How about using Pivot Table.

You can set the source data for the Pivot as a Table so that when the source expands it will be included in the pivot when refreshed, simple right click on table and choose refresh. You could if you wanted have some VBA on the sheet select event that refreshes the pivot when the sheet is activated.

See attached
dummy-sheet-tabe-to-range.xlsx

Author

Commented:
Hi Rob

I added a new row o the source and the pivot table do not show the new row
Not sure if I want to use VBA, prefer not. as sheet has too much going on already
But if we have to , then we have to
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Roy CoxGroup Finance Manager

Commented:
Is there any reason the second sheet's data is not formatted as a Table?

This adds rows to both sheets if they are tables
dummy-sheet-tabe-to-range.xlsm
Finance Analyst
Commented:
Right click on pivot table and select refresh, new data should appear in pivot.

Author

Commented:
Thank Rob... I was missing the refresh.....
Thanks Roy
Rob HensonFinance Analyst

Commented:
No worries, thanks for the feedback.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial