I wouldn't normally hard code sheet names but the way you generate new sheets from a pivot table I can't see another option.

What I’m trying to achieve is that the first part of my macro pulls data from 6 different workbooks into one sheet. I have 9 people who own the data and need to do work on it so am using a pivot table to create a summary of the detail and then creating 8 new worksheets for each person.

It works fine the first time i open the file, and maybe that's how it will have to stay, but would prefer if there was some way to recent the automatic number of new worksheets.