Help many pivot tables now what.
 

I have Excel 2003. I created an allocation sheet to track staff time. All
fields were defined by INSERT NAME FUNCTION and hidden on a separate sheet.
The managers are to enter data via drop down lists with validation. Once
entered, i have created a MACRO were a pivot table is created.
I now need to link those 6 manager speadsheets into one excel sheet. I have
started to do this with V-lookups and links. However, my macros are not
working now and there are numerous tabs at the bottom.
Questions for those that can help!
1.Can I still keep individual sheets saved on a shared drive, and then
somehow incorporate another spreadsheet out on the shared drive, that if
someone enters data into one sheet, it would automatically update?
2.Why now are the Macros not working.
3.Howdo I write a Macro to search for were there is zero data and delete.
Currently, I went to the options tab, and clicked off the show zeros, so they
are hidden.
GOAL-is to make everything as automatic as possible for MANAGERS!
Thanks so much.