Ok, I figured out this seems to be because after creation of the pivot table I want to move it to a specified location using:
The problem is caused when the location has finite number of rows and when the pivot table won't fit there excel seems to create a new sheet.Code:PT.Location = WSD3.Cells(6, 2)
I may need to perhaps put this as another thread but basically I now need my code adapted so it is forced to be x top results. For example top 9 then group all other results together to avoid there being a pivot table over 10 rows of results.
If anyone has any ideas on this please let me know.