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!
VBA Code:
[CODE=vba]Sub createPivotTableExistingSheet()
'Source: Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now. | Power Spreadsheets
'For further information: Excel VBA Create Pivot Table: Step-by-Step Guide and 4 Code Examples
'declare variables to hold row and column numbers that define source data cell range
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myFirstColumn As Long
Dim myLastColumn As Long
'declare variables to hold source and destination cell range address
Dim mySourceData As String
Dim myDestinationRange As String
'declare object variables to hold references to source and destination worksheets, and new Pivot Table
Dim mySourceWorksheet As Worksheet
Dim myDestinationWorksheet As Worksheet
Dim myPivotTable As PivotTable
'identify source and destination worksheets
With ThisWorkbook
'Set mySourceWorksheet = .Worksheets(" Data2")
'Set myDestinationWorksheet = .Worksheets(" Data2")
End With
'obtain address of destination cell range
' myDestinationRange = myDestinationWorksheet.Range("I3").Address(ReferenceStyle:=xlR1C1)
myDestinationRange = Sheet3.Range("I3").Address(ReferenceStyle:=xlR1C1)
'identify row and column numbers that define source data cell range
myFirstRow = 1
myLastRow = 20005
myFirstColumn = 1
myLastColumn = 7
'obtain address of source data cell range
With Sheet3.Cells
mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
End With
'create Pivot Table cache and create Pivot Table report based on that cache
'Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")
Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheet3.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=Sheet3.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")
'add, organize and format Pivot Table fields
With myPivotTable
.PivotFields("Region").Orientation = xlRowField
.PivotFields("Item").Orientation = xlColumnField
With .PivotFields("Total")
.Orientation = xlDataField
'.Position = 1
.Function = xlSum
'.NumberFormat = "#,##0.00"
End With
End With
End Sub
