Need VBA pivot tables help

[Need_Help_Now]

Hi everyone, I am trying to make pivot tables by recording macros but always get this "run time error '5' Invalid procedure call or argument". It recorded it fine but doesn't work when I try and re-run it!

Anyway here is the code can you please help!? Thanks.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"sheet1!R1C1:R1533C22", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet2!R1C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

 

[RoryA]

If you run that macro repeatedly, you are trying to create another pivot table in the same place as the first and with the same name. Could you elaborate on what you are actually hoping the macro will do?

 

[Need_Help_Now]

I am creating a report for someone who has no idea about excel or macros that will update information evertime they open up the sheet.

The reason I'm replacing the pivot table rather than refreshing is because just refreshing mixes up the variables for reasons I can't understand and the only way to update the data with the variables in the right place is to create a new pivot table.

Thanks for your help.

 

[RoryA]

Looking at the code again, if you get an error 5 (rather than 1004) I suspect you either don't have a sheet called Sheet2 or you don't have Sheet1.