dont know how your data is setup but 

Sheets.Add.Name = "Sheet1"
sheet1 is very common...if you try to add a sheet that is when there is one already named sheet1...it will 1004

now the pivot table you area trying to add...is it going to be on the new sheet or the sheet where the data resides? if its where the data resides why are you adding a new sheet?