excel - VBA：更新枢轴问题

Question

我对 VBA 比较陌生，在尝试更新枢轴时，我收到以下错误 Run-time error 1004，枢轴似乎没有正确获取数据集，因为在枢轴上缺少正确的日期在数据集上格式化。

当我运行宏时，它会在 DateVal 行上停止，如果需要，请随时询问更多详细信息。

我尝试手动添加属于数据集一部分的日期，结果相同，但是如果我选择运行记录宏的日期，它会起作用，但是如果我运行由记录器创建的宏，我会得到相同的错误，我希望你可以告诉我这段代码哪里错了。

Sub UpdatePivots()


Application.ScreenUpdating = False
Dim DateVal As String
DateVal = Sheets("home").[G16]
DateFormat = Sheets("home").[Q10]

' Find the last row with data in column A
Sheets("GLViewFinanceCycleCounts").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False 'Remove Filters if exists
LastRow = ActiveSheet.Range("C1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row

pName = ActiveWorkbook.Path      ' the path of the currently active file
wbName = ActiveWorkbook.Name     ' the file name of the currently active file
shtName = ActiveSheet.Name       ' the name of the currently selected worksheet
DatasetRange = Range("A1:BM" & LastRow).Address(ReferenceStyle:=xlR1C1)
DatasetPT = pName & "\[" & wbName & "]" & shtName & "!" & DatasetRange

' Pivot Updates
' CompletesAndSH page
Sheets("CompletesAndSH").Select ' Pivot Table Page
ActiveSheet.PivotTables("PivotTable41").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DatasetPT, Version:= _
        xlPivotTableVersion14)
ActiveWorkbook.RefreshAll
' Filter to selected month
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name").ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name").CurrentPage = DateVal

标签： excelvbapivot-table