Have you ever thought that you can build the pivot table in this very design permanently and keep it hidden and refresh it by a macro according to the new rawdata and make it visible again? This way you wouldn't have to make up a new pivot table by macro everytime an input happens.

If it's ok for you, it's very easy to direct users to give a name to the sheet by the inputbox function.