Broken reference to worksheet

[luirib]

I have a workbook with 6 sheets: 5 are for each of the salespersons and the last one gathers the information of said 5 sheets.
Each week I receive the updated information from each of the salespersons and would like to dump those sheets into my file and get the summary updated automatically. The problem I’m having is that since the summary is picking the information from the original 5 sheets once I put in the new sheets and delete the original ones, all my references are broken and I get a whole bunch of #REF.
Is there to overcome this with or without VBA?
Thanks.

 

[Andrew Poulsom]

Can't you just copy the data from the new sheet into the original sheet rather than inserting and deleting sheets?

 

[luirib]

I could, but I would like overwriting formulas. Also, I said 5 salespersons, but it is actually 35, so the copy/paste would take some time.

 

[jberwald]

I do the same type of reporting and have found that it is easier to have my report reading from a folder rather then the workbook itself - for exactly the reason you state.

I put the incoming worksheets in a folder called, ie. "REPORTS", and put this weeks reports in a folder called "THIS WEEKS REPORTS".

My workbook formulas are set up to read from "THIS WEEKS REPORTS", which you can accomplish initially with a quick REPLACE. Just either delete old worksheets or rename the folder each week, just making sure that what you need is always in the "THIS WEEKS REPORTS" folder.

You are now ready to open up your workbook and update your report.