Hi All,
I need an help to create pivot table in same existing sheet. I have tried creating macros using recording and as well as on the dynamic way. However, I face runtime error at pivot creation part (Attached error images).pivot.JPG
Pivot table in existing sheet creation support required- explained below:
1)In my workbook I have multiple sheets where pivot table should always be created in second sheet - which will be the source data for pivot(sheet will be second sheet after Main data sheet) - Attached the source dump for your reference.
2)one more help required here is- name of sheet changes based on item name(Sheet name starts with space followed by name of item Eg: Item-Pencil). Also, one or two sheets will be hidden in workbook.
Hence I am struggling while creating pivot cache part. In my code, i am trying calling the sheet name with sheet index, while creating pivot sheet and facing the run-time error in below line when i step through the code.
Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheet3.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=Sheet3.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")
kindly validate and provide your help. Thanks for your support in advance!
Reply With Quote