Macro for Pivot table not working properly

Greetings and thank you in advance for all your trouble. The code below creates a pivot table for a set of data that I have. It runs fine except that every time I run it, it creates a new sheet rather than dumping the pivot table in a sheet that I have designated for it called "Pivot Data". Can some one help me to get the code to run without generating a new sheet every time. Also, can some one be so kind as to show me how to adjust the data range so that it is not fixed at 5000 rows - sometimes the data rage is smaller and sometimes it is larger. I thank you for any assistance.
Sub Pivot()

Please Login or Register  to view this content.

Thank you very much for the reply. I do appreciate your help. I have changed the code and I am getting a Run time error '5'. Would any one happen to know what this might refer to?