Cannot create a PivotCache out of the data error 1004. However if I copy and paste the data onto a new worksheet PivotCache works fine

[sumant28]

I was trying to fiddle with xl2bb but I don't know how illuminating it would be to post two exact pieces of data. The excel file is being downloaded from a server rather than being user created. I am following the video tutorial here:

and everything works fine with his example but I cannot create the cache out of the data that I need for analysis. At first I thought it had something to do with the size of the spreadsheet but if I trim the spreadsheet down to a few lines and a few columns the error persists. If I then copy and paste that small range of data to a new worksheet there are no problems. Most online solutions I see to problems involving error 1004 is to check for hidden columns or for empty column headers but I do not know if that applies here