Merging sheets weekly to one workbook for a weekly report

Basically I am taking 1 workbook and I have about 3 reports I need to put into it.  The main sheet is generated from 1 report then turned into a pivot table right now. then added formulas in vlookup cross reference a lookup sheet and return certain values  then I make another pivot table and reports are generated from there

I know it can be done

I just dont know the semantics behind it. 

Currently the steps are

report 1 is run

copy pasted to a new workbook

columns are added to that sheet then report 2 is brought in for more info the report 3 is brought in as cross reference. 

then a 4th report is brought in to cross reference that one.  
Then I make a pivot table and report out.

I can and will get into more detail as asked but I need to know If it is basically making a template with the formulas, 

if those formulas can transfer weekly to the new report as long as my vlookup names are the same file name each week (overwriting each of the other reports as they are generate as to not duplicate). and then I can save as a PDF to not lose the info I report out each week.