创建具有定义工作表名的动态枢轴工作表
创建具有定义工作表名的动态枢轴工作表
提问于 2022-12-01 12:55:53
**我试图用这个宏代码创建三个不同的表。因此，当我运行这段代码时，这些工作表应该创建，但我希望将这些表重命名为特定名称，并删除它们，或者在再次运行代码时替换它们。因此，下面的代码是以这样的方式修改的，它创建了2个枢轴表和一个包含数据的工作表，从而创建了定义范围的计数.与国
因此，当我在互联网上搜索另一种代码时，我尝试了其他代码，但是在创建pivot表时，范围(动态)并没有被选中。它会抛出一个错误
SetwsPT=wb.Worksheets.Add请帮帮忙。
Sub MacroPivotReceivedResolved()
Sheets.Add
pivotWS = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"ReceivedMacro!R6C1:R20000C54", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:=pivotWS & "!R3C1", TableName:="PivotTable5" _
, DefaultVersion:=xlPivotTableVersion15
Sheets(pivotWS).Select
Cells(3, 3).Select
ActiveSheet.PivotTables("PivotTable5").RowAxisLayout xlTabularRow
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Receipt Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("Receipt Date"), "Count of Case Age", xlCount
Sheets("ResolvedMacro").Select
Range("A6").Select
Sheets.Add
pivotWS1 = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"ResolvedMacro!R6C1:R20000C54", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:=pivotWS1 & "!R3C1", TableName:="PivotTable6" _
, DefaultVersion:=xlPivotTableVersion15
Sheets(pivotWS1).Select
Cells(3, 3).Select
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Resolved Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
"PivotTable6").PivotFields("Resolved Date"), "Count of Case Age", xlCount
Sheets("ReceivedMacroAge").Select
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlLTR
.MergeCells = False
End With
Columns("A:A").ColumnWidth = 16.29
Cells.Select
Selection.ColumnWidth = 17.57
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Range("J2").Select
ActiveCell.FormulaR1C1 = "=COUNT"
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("H1").Select
ActiveCell.FormulaR1C1 = "Total Outstanding"
Range("I1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[5]C)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "Over 8 Weeks (Over 56 Days)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS(R[9]C:R[1000]C, "">=57"")"
Range("H3").Select
ActiveCell.FormulaR1C1 = "6-8 Weeks (42-56 days)"
Range("I3").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(INT(R[9]C:R[1000]C>=42), INT(R[9]C:R[1000]C<57))"
Range("H4").Select
ActiveCell.FormulaR1C1 = "4-6 weeks (28 - 41)"
Range("I4").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(INT(R[9]C:R[1000]C>=28), INT(R[9]C:R[1000]C<42))"
Range("H5").Select
ActiveCell.FormulaR1C1 = "2-4 Weeks (14 - 27)"
Range("I5").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(INT(R[9]C:R[1000]C>=14), INT(R[9]C:R[1000]C<28))"
Range("H6").Select
ActiveCell.FormulaR1C1 = "0-2 Weeks (0-13)"
Range("I6").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(INT(R[9]C:R[1000]C>=1), INT(R[9]C:R[1000]C<14))"
Range("H7").Select
ActiveCell.FormulaR1C1 = "Cases to breach next day ( Day 56)"
Range("I7").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS(R[9]C:R[1000]C, ""=56"")"
Range("H8").Select
End Sub回答 1
Stack Overflow用户
发布于 2022-12-02 00:17:37
以下是如何删除和替换工作表的基本示例：
Sub MacroPivotReceivedResolved()
Const PIVOTA_NAME As String = "Pivot A"
Dim wsPivot As Worksheet, wb As Workbook, pc As PivotCache, pt As PivotTable
Set wb = ThisWorkbook 'for example
DeleteSheet wb, PIVOTA_NAME 'delete the sheet if it exists
Set wsPivot = wb.Sheets.Add 'add new sheet for pivot table
wsPivot.Name = PIVOTA_NAME
'create the pivot cache
Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="ReceivedMacro!R6C1:R20000C54", _
Version:=xlPivotTableVersion15)
'create the pivot table
Set pt = pc.CreatePivotTable(TableDestination:=pivotWS.Range("A3"), _
TableName:="PivotTable5", _
DefaultVersion:=xlPivotTableVersion15)
'now you can use `pt` instead of `ActiveSheet.PivotTables("PivotTable5")`
pt.RowAxisLayout xlTabularRow
With pt.PivotFields("Receipt Date")
.Orientation = xlRowField
.Position = 1
End With
pt.AddDataField pt.PivotFields("Receipt Date"), "Count of Case Age", xlCount
End Sub
'Remove any worksheet named `wsName` from workbook `wb`,
' ignoring any error if no sheet with that name is found
Sub DeleteSheet(wb As Workbook, wsName As String)
Dim ws As Worksheet, da As Boolean
On Error Resume Next 'ignore error if sheet doesn't exist
Set ws = wb.Worksheets(wsName)
On Error GoTo 0 'stop ignoring errors
If Not ws Is Nothing Then
da = Application.DisplayAlerts 'get current setting
Application.DisplayAlerts = False 'turn off alerts
wb.Worksheets(wsName).Delete
Application.DisplayAlerts = da 'restore previous setting
End If
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74642212
复制相关文章
相似问题
腾讯云开发者
