VBA - Pivot Cache错误 [英] VBA - Pivot Cache error
问题描述
您好,
我正在尝试使用动态范围编写用于在现有电子表格中创建数据透视表的代码,但仍然遇到运行时错误5而不能似乎让它发挥作用。这比预期的要长,并且感到沮丧...任何输入都是
很棒。
 
Sub Create_Pivot()
'
'Create_Pivot Macro
'
随申请表
      .ScreenUpdating = False
      .EnableEvents = False
 结束与$
'
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim mySourceData As String
Dim myDestinationRange As String
Dim mySourceWorksheet As Worksheet
Dim myDestinationWorksheet As Worksheet
将myPivotTable调暗为数据透视表
使用ThisWorkbook
     设置mySourceWorksheet = .Worksheets(" Sum_Data")
     设置myDestinationWorksheet = .Worksheets(" Pivot")
 结束用$
myDestinationRange = myDestinationWorksheet.Range(" A1")。地址(ReferenceStyle:= xlR1C1)
myFirstRow = 1
myFirstColumn = 1
使用mySourceWorksheet.Cells
myLastRow = .Find(What:=" *" ,LookIn:= xlFormulas,LookAt:= xlPart,SearchOrder:= xlByRows,SearchDirection:= xlPrevious).Row
myLastColumn = .Find(What:=" *",LookIn:= xlFormulas, LookAt:= xlPart,SearchOrder:= xlByColumns,SearchDirection:= xlPrevious).Column
mySourceData = .Range(.Cells(myFirstRow,myFirstColumn),. Cells(myLastRow) ,myLastColumn))。地址(ReferenceStyle:= xlR1C1)
$
结束用于
设置myPivotTable = ThisWorkbook .PivotCaches.Create(SourceType:= xlDatabase,SourceData:= mySourceWorksheet.Name& mySourceData).CreatePivotTable(TableDestination:= myDestinationWorksheet.Name& myDestinationRange,TableName:=" PivotTableNew")
用m yPivotTable
使用.PivotFields(" Class")。Orientation = xlRowField
使用.PivotFields(" Jan-18")
。 Orientation = xlDataField
.Position = 1
.Functiom = xlSum
.NumberFormat ="#,## 0.00"
结束与
结束与$
      申请时
      .ScreenUpdating = True
      .EnableEvents = True
 结束与$
 截止日期为
结束子
缓存上发生错误
您好Candacem,
对于您的问题,它是由SourceData的无效参数引起的。它应该是Sum_Data!R1C1:R5C5。
我建议您使用以下代码进行测试:
Sub Create_Pivot()
'
'Create_Pivot Macro
'
使用应用程序
.ScreenUpdating = False
.EnableEvents = False
以$ b结尾$ b'
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim mySourceData As String
Dim myDestinationRange As String
Dim mySourceWorksheet As Worksheet
Dim myDestinationWorksheet As Worksheet
Dim myPivotTable As PivotTable
With ThisWorkbook
Set mySourceWorksheet = .Worksheets(" Sum_Data")
设置myDestinationWorksheet = .Worksheets(" Pivot")
End with
myDestinationRange = myDestinationWorksheet.Range(" A1")。Address(ReferenceStyle:= xlR1C1)
myFirstRow = 1
myFirstColumn = 1
Wit h mySourceWorksheet.Cells
myLastRow = .Find(What:=" *",LookIn:= xlFormulas,LookAt:= xlPart,SearchOrder:= xlByRows,SearchDirection:= xlPrevious).Row
myLastColumn = .Find(What:=" *",LookIn:= xlFormulas,LookAt:= xlPart,SearchOrder:= xlByColumns,SearchDirection:= xlPrevious).Column
'mySourceData =。范围(.Cells(myFirstRow,myFirstColumn),. Cells(myLastRow,myLastColumn))。地址(ReferenceStyle:= xlR1C1)
mySourceData = .Range(.Cells(1,1),。Cellls(5,5) ).Address(ReferenceStyle:= xlR1C1)
以
结尾 设置myPivotTable = ThisWorkbook.PivotCaches.Create( SourceType:= xlDatabase,SourceData:= mySourceWorksheet.Name& "!" &安培; mySourceData)_
.CreatePivotTable(TableDestination:= myDestinationWorksheet.Name&"!"& myDestinationRange,TableName:=" PivotTableNew")
使用myPivotTable
使用.PivotFields(" Class")。Orientation = xlRowField
使用.PivotFields(" Jan-18")
.Orientation = xlDataField
.Position = 1
.Functiom = xlSum
.NumberFormat ="#,## 0.00"
结束
结束
申请
.ScreenUpdating = True
.EnableEvents = True
结束
结束
结束次级
最好的问候,
Edward
Hi There,
I am trying to write code for creating a pivot table in an existing spreadsheet using a dynamic range but keep getting a runtime error 5 and can't seem to get it to work. This is taking way longer than it should and getting frustrated...any input would be great.
Sub Create_Pivot()
'
' Create_Pivot Macro
'
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim mySourceData As String
Dim myDestinationRange As String
Dim mySourceWorksheet As Worksheet
Dim myDestinationWorksheet As Worksheet
Dim myPivotTable As PivotTable
With ThisWorkbook
Set mySourceWorksheet = .Worksheets("Sum_Data")
Set myDestinationWorksheet = .Worksheets("Pivot")
End With
myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
myFirstRow = 1
myFirstColumn = 1
With mySourceWorksheet.Cells
myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
End With
Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & myDestinationRange, TableName:="PivotTableNew")
With myPivotTable
With .PivotFields("Class").Orientation = xlRowField
With .PivotFields("Jan-18")
.Orientation = xlDataField
.Position = 1
.Functiom = xlSum
.NumberFormat = "#,##0.00"
End With
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End With
End Sub
There error is happening on the cache
Hi Candacem,
For your issue, it is caused by invalid parameter for SourceData. It should be Sum_Data!R1C1:R5C5.
I suggest you make a test with below code:
Sub Create_Pivot() ' ' Create_Pivot Macro ' With Application .ScreenUpdating = False .EnableEvents = False End With ' Dim myFirstRow As Long Dim myLastRow As Long Dim myFirstColumn As Long Dim myLastColumn As Long Dim mySourceData As String Dim myDestinationRange As String Dim mySourceWorksheet As Worksheet Dim myDestinationWorksheet As Worksheet Dim myPivotTable As PivotTable With ThisWorkbook Set mySourceWorksheet = .Worksheets("Sum_Data") Set myDestinationWorksheet = .Worksheets("Pivot") End With myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1) myFirstRow = 1 myFirstColumn = 1 With mySourceWorksheet.Cells myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 'mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1) mySourceData = .Range(.Cells(1, 1), .Cells(5, 5)).Address(ReferenceStyle:=xlR1C1) End With Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData) _ .CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableNew") With myPivotTable With .PivotFields("Class").Orientation = xlRowField With .PivotFields("Jan-18") .Orientation = xlDataField .Position = 1 .Functiom = xlSum .NumberFormat = "#,##0.00" End With End With With Application .ScreenUpdating = True .EnableEvents = True End With End With End Sub
Best Regards,
Edward
这篇关于VBA - Pivot Cache错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!