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屋！