How do I combine excel sheets multiple tabs to one workbook and automatically update (See below for more info)

Question

I have two excel files (workbooks ). Book1 has multiple tabs/ sheets. I need to copy all the tabs to my other workbook(Book2), also it should go to only 1 sheet. Not multiple sheets.

Also I will be editing the Book1 and will be adding more rows to the sheets. And it should reflect in the Book2.

I was able to combine all data from Book1 to one sheet. But I'm not able to add new rows and get it to reflect in Book2. Please let me know if there is a way to do this.

Thanks in advance.

Answer 1

Let's say that you want to combine columns A to K, from row 2 down to the last used row in all sheets of Book1. And that column A will be populated in all used rows.

Make sure that both workbooks are open in Excel.

You can use a formula like this in A2 on the worksheet of Book2:

=VSTACK(
  FILTER('[Book1.xlsx]Sheet1'!$A$2:$K$10000, '[Book1.xlsx]Sheet1'!$A$2:$A$10000<>"", ""), 
  FILTER('[Book1.xlsx]Sheet2'!$A$2:$K$10000, '[Book1.xlsx]Sheet2'!$A$2:$A$10000<>"", ""))

Change the name of the workbook and worksheets to the real names. Add more sheets as needed.

Close Book1. Excel will add the path of Book1 to the workbook name in the formula.

Save Book2.

Answer 2

Hi,

Follow the instructions in this video - https://www.youtube.com/watch?v=yL11ugShdrk