Splitting Excel file but retaining lookup data and formulas

Splitting Excel file but retaining lookup data and formulas

I have a master expenses file that has a page for each employee
summarising their expenses. The employee sheets run off a hidden data
sheets.
I want to send each employee a copy of their expenses with the lookups etc
hidden but still in tact so that they can complete the coding etc using
lookup lists like project and expense codes from the data sheets.
What I envisage is a macro create to a set of individual employee files
with only their page but with the lookups and data in each remaining in
tact and hidden. I've been trying to create a macro to systematically work
through the file creating new files for each employee deleting each of the
other employee sheets but retaining the hidden data and related lookups -
with no success
Any help appreciated.