Create Excel 2010 VBA pivot table in separate worksheet does not work
I was creating a report using VBA in Excel. But when I try to create a pivot table on a specific sheet, it is not created and the error message "Runtime error" 424 "Required object" appears. I posted my code here, please tell me what the problem is.
Private Sub CommandButton1_Click()
createPivot
End Sub
Sub createPivot()
' Creates a PivotTable report from the table on studentmarks
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.
Dim objTable As PivotTable, objField As PivotField
' Select the sheet and first cell of the table that contains the data.
ActiveWorkbook.Sheets("studentmarks").Select
Range("A1").Select
Set objTable = reports.PivotTableWizard
''''Set objTable = Sheet1.PivotTableWizard // if I give sheet1 instead of reports it is working but every time it is creating new worksheets
objTable.ColumnGrand = False
' Specify a page field.
Set objField = objTable.PivotFields("subject")
objField.Orientation = xlPageField
' Specify row and column fields.
Set objField = objTable.PivotFields("name")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("subject")
objField.Orientation = xlColumnField
Set objField = objTable.PivotFields("total")
objField.Orientation = xlDataField
End Sub
I need to create a pivot table in a report worksheet Please help me ..
+3
source to share
1 answer
From your question, I think you want something like this.
Dim wsTarget As Worksheet
Dim rngSource As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim field As PivotField
Set rngSource = Sheets("studentmarks").Range("A1").CurrentRegion
Set wsTarget = Sheets("reports")
wsTarget.Select
For Each pt In wsTarget.PivotTables
pt.TableRange2.Clear
Next pt
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, rngSource, xlPivotTableVersion14)
Set pt = pc.CreatePivotTable(wsTarget.Range("A1"), "PivotTable1", , xlPivotTableVersion14)
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("subject")
field.Orientation = xlPageField
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("subject")
field.Orientation = xlColumnField
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("name")
field.Orientation = xlRowField
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("total")
field.Orientation = xlDataField
This code will create a pivot table inside the report.
I hope this works for you.
+3
source to share