我正在尝试编写一个宏,当新数据被放入原始表中时,它会更新我的透视表。我一直收到一个错误,不知道如何修复它。这是我到目前为止所掌握的。
Sub UpdatePivotTableRange()
Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim lastRow As Long
Set Data_Sheet = ThisWorkbook.Worksheets("Data insert")
Set Pivot_Sheet = ThisWorkbook.Worksheets("CC_Users")
PivotName = PivotTable6 Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)
Pivot_Sheet.PivotTables (PivotName)
ChangePivotCache.ActiveWorkbook PivotCaches.Create SourceType:=xlDatabase, SourceData:=NewRange Pivot_Sheet.PivotTables(PivotName).RefreshTable
Pivot_Sheet.Activate MsgBox "Your Pivot table is now updated" End Sub
谢谢
发布于 2018-06-19 05:08:05
这需要粘贴在VBE的工作表上,该工作表包含要更改的表。
这样,当工作表上的任何单元格的发生更改时,这将刷新工作簿上存在的每个数据透视表(以及数据透视表)。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.RefreshAll
Application.DisplayAlerts = True
Application.EnableEvents = True
Msgbox "All Pivots Refreshed"
End Sub
https://stackoverflow.com/questions/50917069
复制相似问题