VBA Code to pull data dynamically from multiple sheet tabs

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