Excel VBA-概括枢轴源数据/范围 [英] Excel VBA - Generalize Pivot Source Data / Range

问题描述

我记录了数据透视表宏，我试图概括源数据，而不是从工作表名称"REPORTS"开始

I recorded the pivot table macro and I'm trying to generalize source data instead of going off of sheet name "REPORTS"

无论工作表的名称如何，它都会从活动工作表中获取所有数据.

It grabs all the data from active sheet despite what the name of the sheet.

这样，我可以使用宏为任何活动工作表创建数据透视表:-

Sheets("**REPORTS**").Select
Range("A1").Select
Sheets.Add.Name = "Pivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Sheets("**REPORTS**").Range("A1").CurrentRegion, Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion15
Sheets("Pivot").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With

推荐答案

Sub TT()

    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim pc As PivotCache

    Set shtSrc = ActiveSheet

    Set shtDest = shtSrc.Parent.Sheets.Add()
    shtDest.Name = shtSrc.Name & "-Pivot"

    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=shtSrc.Range("A1").CurrentRegion)
    pc.CreatePivotTable TableDestination:=shtDest.Range("A3"), _
        TableName:="PivotTable1"

    With shtDest.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With

End Sub

这篇关于Excel VBA-概括枢轴源数据/范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！