Problem deleting cell while shifting data up and keeping empt cells

When I delete the data of a tem member in the team alignment tab and shift the data up the worksheet loses its empty tabs. Also When I go to the corresponding team tab sheet the deleted team member has "ref" in the cell where their name used to be. Over all I need the data in the team alignment to shift upwards when I remove a team member and also I need the names and their corresponding data to shift up on their team tabs simultaneously. Any help would be greatly appreciated as I am mostly a novice, thank you in advance.

For B6

Formula:

Please Login or Register  to view this content.

For B29 and each first row of month

Formula:

Please Login or Register  to view this content.

drag down to end
did it on sheet Ruchti for you

Kind regards
Leo

That code you gave me seems to work and now its not showing the "ref' when you delete the TM in the team alignment tab so thank you for that. I do need more than anything for the cells in the corresponding team tabs to delete and shift up when I clear or delete the cell in the team alignment tab. Other than that I appreciate the help you have given me thus far. The code you gave me looks a little more complex than the one I have now but works better than the one I have now too, thank you.