2 Attachment(s)
RE: Pivot table creation in existing sheet- VBA issues
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).Attachment 27349
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!
1 Attachment(s)
RE: Pivot table creaton in existing sheet --VBA
Hi Paul_Hossler,
My current macro creates the pivot table only for one sheet Item - Pencil using P45cal piece of code it works fine for this sheet.
I need some more additional feature like for each sheet, pivot tables to be created as you stated below:
Do you want 4 pivot tables, one pen each Item worksheet and using the data on that sheet - yes. IF my workbook have 5 sheets, then 5 pivot tables tobe created using the data on the sheet with same pivot fields as per current macro.
Item -column field
Region- Row field
Total - Values field (as sum)
two conditions:
Sheet name will vary and change hence we need to refer in this way sheet1 or sheet index.
macro should start creating pivot table from second sheet i.e, except Main Data, it should create pivot for all sheets using the data on that sheet.