VBA to pull data dynamically from multiple sheets

[Tahas]

Hello All,
I have a created a spreadsheet for our sales team. There are five sales staff and I have assigned each with one tab so they can update their sales data which occurs several times a day. I want excel to dynamically pull data from each tab and when it goes to last row for a particular tab (say sheet1), then it moves to the next tab (sheet2) and so on. I used following formula to pull data but do not know how to instruct excel to look for the last row and move to next sheet. Here is my formula that I used but I think a VBA would be a better option:
=HLOOKUP(B$1,INDIRECT("Ivy"&"!A1:BT500"),ROWS(B$1:B1)+1,FALSE)

Any help will be highly appreciated!
Taha

 

[Larry Haydn]

Let me paraphrase:
You want to pull all rows from each of the 5 Sales tab,
and consolidate then into another tab?

Question:
What would you wish to do with each of the 5 Sales data after you pulled them into a consolidated sheet?
Leave them there, or delete them?

Why?
If you leave them there, you will need to know which data has already been consolidated previously,
so that you just pull new data.

 

[Tahas]

Hello Larry,
I don't want to delete the tabs as all five tabs are constantly being updated by the sales staff. I will create a macro to auto refresh the "Consolidated Sheet" in real-time as sales staff update the data.

 

[Larry Haydn]

I do not mean delete the tabs, but delete the data in those tabs.

 

[alansidman]

A quick way to do this is to Append all the sheets into one table using Power Query. Here is a link to show you how easy this is.

Append sheets in Power Query - Google Search

 

[Tahas]

I do not mean delete the tabs, but delete the data in those tabs.

Yes, I don't want to delete the data. I want to auto refresh to fetch all data including new updates/changes etc.

 

[Tahas]

A quick way to do this is to Append all the sheets into one table using Power Query. Here is a link to show you how easy this is.

Append sheets in Power Query - Google Search

Thanks a lot alansidman, I will try and provide an status update.

 

[Tahas]

alansidman

Thanks so much alansidman. I used Power Query and it worked like a charm. You gave me a better insight and now I am going to use Power BI to pull data directly and create interactive visualization dashboard. You are amazing!