I have some data which is the source for a pivot table I am building with following VBA script.
我有一些数据，它们是我使用VBA脚本构建的pivot表的源。
The code is creating a new sheet and generating pivot table there but I want the pivot table to be present in the same sheet where source data is.
代码正在创建一个新的表，并在那里生成pivot表，但是我希望数据透视表出现在源数据所在的相同的表中。
It also requires to check till where existing data is and have to select an empty cell.
它还需要检查现有数据的位置，并必须选择一个空单元格。
Sub ApplyPivot()
ActiveWorkbook.Sheets("NOIDA").Select
Range("A1").Select
Set objTable = Sheet1.PivotTableWizard
Set objField = objTable.PivotFields("sector")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("number")
objField.Orientation = xlColumnField
Set objField = objTable.PivotFields("quantity")
objField.Orientation = xlDataField
objField.Function = xlCount
objField.NumberFormat = " ######"
End Sub
1 个解决方案
#1
1
Here's a very basic example of how to create a Pivot Table in code based on a Range of source data - it's just a couple of extra lines rather than use the wizard per the code in your question.
这里有一个非常基本的例子，说明如何基于一系列的源数据在代码中创建Pivot表——它只是多了几行代码，而不是按照问题中的代码使用向导。
In order to place the table clear of your source data it is checking the number of columns in the source data and then adding 2 columns i.e. there will be a single empty column between the source data and the pivot table.
为了将表清除源数据，它检查源数据中的列数，然后添加2列，也就是说，源数据和pivot表之间只有一个空列。
Option Explicit
Sub ApplyPivotOnSameSheet()
Dim wsTarget As Worksheet
Dim rngData As Range
Dim rngPivotTarget As Range
Dim objCache As PivotCache
Dim objTable As PivotTable
Dim objField As PivotField
'create pivot table in code (no wizard) on same sheet
Set wsTarget = ThisWorkbook.Sheets("NOIDA")
'set range to build table from
Set rngData = wsTarget.Range("A1").CurrentRegion
'set range for left-hand corner of pivot clear of source data by 1 column
Set rngPivotTarget = wsTarget.Cells(1, rngData.Columns.Count + 2)
'create cache from data
Set objCache = ThisWorkbook.PivotCaches.Create(xlDatabase, rngData)
'create table from cache
Set objTable = objCache.CreatePivotTable(rngPivotTarget)
'your original code
Set objField = objTable.PivotFields("sector")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("number")
objField.Orientation = xlColumnField
Set objField = objTable.PivotFields("quantity")
objField.Orientation = xlDataField
objField.Function = xlCount
objField.NumberFormat = " ######"
End Sub
#1
1
Here's a very basic example of how to create a Pivot Table in code based on a Range of source data - it's just a couple of extra lines rather than use the wizard per the code in your question.
这里有一个非常基本的例子，说明如何基于一系列的源数据在代码中创建Pivot表——它只是多了几行代码，而不是按照问题中的代码使用向导。
In order to place the table clear of your source data it is checking the number of columns in the source data and then adding 2 columns i.e. there will be a single empty column between the source data and the pivot table.
为了将表清除源数据，它检查源数据中的列数，然后添加2列，也就是说，源数据和pivot表之间只有一个空列。
Option Explicit
Sub ApplyPivotOnSameSheet()
Dim wsTarget As Worksheet
Dim rngData As Range
Dim rngPivotTarget As Range
Dim objCache As PivotCache
Dim objTable As PivotTable
Dim objField As PivotField
'create pivot table in code (no wizard) on same sheet
Set wsTarget = ThisWorkbook.Sheets("NOIDA")
'set range to build table from
Set rngData = wsTarget.Range("A1").CurrentRegion
'set range for left-hand corner of pivot clear of source data by 1 column
Set rngPivotTarget = wsTarget.Cells(1, rngData.Columns.Count + 2)
'create cache from data
Set objCache = ThisWorkbook.PivotCaches.Create(xlDatabase, rngData)
'create table from cache
Set objTable = objCache.CreatePivotTable(rngPivotTarget)
'your original code
Set objField = objTable.PivotFields("sector")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("number")
objField.Orientation = xlColumnField
Set objField = objTable.PivotFields("quantity")
objField.Orientation = xlDataField
objField.Function = xlCount
objField.NumberFormat = " ######"
End Sub