VBA枢轴表出现在特定的表上

Currently I have this code and it runs smoothly. But, the Pivot created seems did not appear on "ST_TicketPivot" sheet that I created.

现在我有了这段代码，它运行得很顺利。但是，似乎没有出现在我创建的“ST_TicketPivot”表上。

So, how can I make the Pivot appear on sheet that I want?

那么，如何使主元出现在我想要的表上呢?

Private Sub ST_Tickets()
    Dim objTable As PivotTable, objField As PivotField
    ActiveWorkbook.Sheets("Paste Record").Select
    Range("A1").Select

    Sheets.Add.Name = "ST_TicketsPivot"
    Sheets("ST_TicketsPivot").Activate


    Set objTable = Sheet1.PivotTableWizard
    objTable.PivotCache.MissingItemsLimit = xlmissingItemNone
    objTable.PivotCache.Refresh

    Set objField = objTable.PivotFields("Priority")
    objField.Orientation = xlColumnField

    Set objField = objTable.PivotFields("Status")
    objField.Orientation = xlRowField

    Set objField = objTable.PivotFields("Contact Name") ' change with type
    objField.Orientation = xlRowField

    Set objField = objTable.PivotFields("Salesforce Case Number")
    objField.Orientation = xlDataField
    objField.Function = xlCount

End Sub

1 个解决方案

#1

It is because the PivotWizard create a new sheet to place the Pivot Table, so you'll have to rename it by using objTable.Parent.Name.

因为PivotWizard创建了一个新表来放置Pivot表，所以您必须使用objTable.Parent.Name来重命名它。

Give this a try :

尝试一下:

Private Sub ST_Tickets()
    Dim objTable As PivotTable, objField As PivotField, Ws As Worksheet
    ActiveWorkbook.Sheets("Paste Record").Range("A1").Select

    Set Ws = Sheets.Add
    Ws.Name = "ST_TicketsPivot"

    Set objTable = Sheet1.PivotTableWizard(TableDestination:=Ws.Cells(3, "A"))
    objTable.PivotCache.MissingItemsLimit = xlmissingItemNone
    objTable.PivotCache.Refresh

    Set objField = objTable.PivotFields("Priority")
    objField.Orientation = xlColumnField

    Set objField = objTable.PivotFields("Status")
    objField.Orientation = xlRowField

    Set objField = objTable.PivotFields("Contact Name") ' change with type
    objField.Orientation = xlRowField

    Set objField = objTable.PivotFields("Salesforce Case Number")
    objField.Orientation = xlDataField
    objField.Function = xlCount

End Sub

#1