I have been working on developing a macro to create profiles for individuals from a database that has data from the previous year. I have the user copy the data onto a data tab in my workbook. I then have the user press a button on an instruction tab to start the macro. They then proceed to select the tab in which the data is on and the year of the report they are creating for the profiles. The macro then makes pivot tables for the profiles to be created from and then each individual in the pivot table gets their own tab for their profile. Seems simple enough. Instead of re creating each profile each time, I have a template that has formulas that reference the pivot table and based on the name entered in on the template, is the location in the pivot table to go retrieve the data. I then COPY the template sheet to the EOF, rename the sheet, pastespecial just the values on the profile (so they won't change) and then move to the next individual in the pivot table and start the process all over again.

Here's my issue.....If I run my macro, save my file, and then re-open my file to run a new report, everything works properly except for the fact that each of my profiles now has #N/A in place of all the cells that used to have values in them. I noticed that my macro inserts the formulas correctly into the template but it is when I COPY the spreadsheet to the EOF that my values all change to #N/A. I know this because my template will always show the profile of the last person in the pivot table, and all of these values are correct. I also get the same sort of issue, when I open my initial copy, save it, close it, and re-open it again I get the exact same issue. There seems to be something wrong with the workbook when I save it and the close/reopen it. Here's the catch...If I run into this issue, all I have to do is run my macro to get all the profiles with #N/A, delete all the profiles and any other modifications that were made to get back to the original data and templates, and re-run the macro. If I do this, then all of the profiles show the proper values and there are no #N/A...

Also, if I remove the pastespecial from my code and just leave the functions in the cells, when I get the #N/A in the cells of each profile all I have to do is click on the cell, click on the function bar, and then click out of the cell to get the value to update correctly... So is there a setting somewhere that needs to be change because it sure doesn't seem like an issue with my code??


Any thoughts would be greatly appreciated....