VBA 아주 쉽게 PivotTable 생성 후 Pivot Chart 만들고 Slicer를 추가하는 Code

Excel VBA 로 PivotTable 생성 

PivotChart 생성

 

Pivot Slicer 생성

 

Sub PivotTableManager()

    '=============================================================================
    With Application
        .ScreenUpdating = False  '매크로의 실행이 화면에 안 나타나도록 함
        .DisplayAlerts = False  '클립보드 경고 메시지 안나타남
        .Calculation = xlCalculationManual ' 자동 계산 방지
    End With
    '=============================================================================

    Dim m as WorkBook
    Set m = Workbooks(ThisWorkbook.Name)

    Dim shtPivot as WorkSheet
    Dim shtData as WorkSheet

    Set shtPivot = m.Sheets("PivotSheet")
    Set shtData = m.Sheets("RawData")

    Dim xWs As Worksheet
    Dim xPT As PivotTable
    For Each xWs In Application.ThisWorkbook.Worksheets
        For Each xPT In xWs.PivotTables
            xWs.Range(xPT.TableRange2.Address).Delete shift:=xlUp
        Next
    Next

    '결함 분석 결과의 Slicer 삭제
    Dim shp As Shape
    For Each shp In shtPivot.Shapes 'Slicer 사전에 미리 삭제
        If shp.Type = msoSlicer Then 
            shp.Delete
        End If
    Next shp


    ' 해당 시트에 있는 모든 Chart 삭제
    For Each cdel In shtData.Shapes
        If cdel.Type = msoChart Then 
            cdel.Delete
        End If
    Next

    Dim row as Integer
    Dim col as Integer

    ' 마지막 위치 Colum, Row 구함
    col = shtData.Cells(1, Columns.Count).End(1).Column
    row = shtData.Cells(Rows.Count, 10).End(3).row    


    ' 피벗에 담을 범위 선언
    Dim rng as Range
    Set rng = shtData.Range(shtData.Cells(1, 1), shtData.Cells(r, c))

    ' 피벗에 담을 범위 경로 만들기 ex) "RawData!R1C1:R19C33
    ' R1C1:R19C33 == A1:AG19
    Dim srcData as String
    srcData = shtData.Name & "!" & rng.Address(ReferenceStyle:=xlR1C1)


    ' 피벗 테이블 뿌려줄 위치 값 저장 ex) "PivotSheet!R12C2"
    Dim strTarget as String 
    strTarget = shtPivot.Name & "!" & shtPivot.Range("B12").Address(ReferenceStyle:=xlR1C1)


    ' 피벗 테이블 생성에 필요한 캐시 생성
    Dim pvtCache As PivotCache
    Set pvtCache = m.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=srcData)

    ' 피벗 테이블 생성
    ' CreatePivotTable(TableDestination, TableName)
    Dim pvt as PivotTable
    Set pvt = pvtCache.CreatePivotTable(strTarget, "PT1")

    With pvt
        .PivotFields("이름").Orientation = xlPageField       ' xLPageField : 필터
        .PivotFields("주소").Orientation = xlPageField       ' xLPageField : 필터
        .PivotFields("당첨여부").Orientation = xlPageField   ' xLPageField : 필터
        
        .AddDataField .PivotFields("당첨여부"), "총합", xlCount  ' xlCount : 값 갯수
        
        .PivotFields("건설사명").Orientation = xlRowField       ' xlRowField : 행
        .PivotFields("모델명").Orientation = xlRowField         ' xlRowField : 행

        .PivotFields("건설사명").ShowDetail = False             ' ShowDetail 접기 / 펴기 
        .PivotFields("모델명").ShowDetail = False               ' ShowDetail 접기 / 펴기 
        
        .Name = "TestPivot"  ' Pivot 이름
        .PivotFields("건설사명").AutoSort Order:=xlDescending, Field:="총합" ' 총합을 기준으로 내림차순 정렬
        .PivotFields("모델명").AutoSort Order:=xlDescending, Field:="총합"   ' 중첩으로 정렬할 수 있음

    End With


    Dim pv2 As PivotTable

    ' 두 번째 피벗 테이블 생성
    shtPivot.PivotTables("TestPivot").TableRange2.Copy Destination:=shtPivot.Range("E5")

    Dim s As PivotTable
    For Each s In shtPivot.PivotTables
        If Not s.Name = "TestPivot" Then
            s.Name = "Two_TestPivot"
            Set pv2 = s
            Exit For
        End If
    Next

    '★ 두 번째 피벗 내용 넣기
    pv2.ClearTable
    With pv2
        .PivotFields("주소").Orientation = xlPageField
        .PivotFields("건설사명").Orientation = xlPageField
        .PivotFields("당첨여부").Orientation  = xlRowField
        .AddDataField .PivotFields("당첨여부"), "총합", xlCount   ' xlCount : 값 갯수

        .PivotFields("당첨여부").ShowDetail = False               ' ShowDetail 접기 / 펴기 

    End With

    ' 피벗 차트 생성하기
    Dim rngCht As Range
    Set rngCht = shtPivot.Range("E3:Q30") '<-- 원하는 차트 크기만큼 범위 지정
    
    ' 차트 생성
    ' Make_PivotChart(PivotTable, Chart_Title, 위치)
    Call Make_PivotChart(pvtC, strAppN, rngCht)


    ' 슬라이서 추가
    ' makeSlicer(PivotTable, 필드이름, 위치)
    Call makeSlicer(pvtC, strMo, shtPivot.Range("S4:V20"))

    shtPivot.Activate
    shtPivot.Cells(1, 1).Select

    
    MsgBox "총 " & Format(Timer - oldTime, "#0.00") & " : 초가 소요되었습니다.", , "악마성"
    '======================================================
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    '======================================================

End Sub

Sub Make_PivotChart(ByRef refPivotTable As PivotTable, ByRef chartTitle As String, ByRef refChartLocation As Range)
    Dim shtPivot As Worksheet
    Set shtPivot = Workbooks(ThisWorkbook.Name).Sheets("PivotSheet")
    Dim shpCht As ChartObject
    Dim cht As Chart

    ' 차트 생성 Pivot Table 영역 선택 후 Add Chart
    Set shpCht = shtPivot.ChartObjects.Add( _
                Left:=refChartLocation.Left, _
                    Width:=refChartLocation.Width, _
                        Top:=refChartLocation.Top, _
                            Height:=refChartLocation.Height)
    Set cht = shpCht.Chart   ' Chart Objects를 Chart 개체로 재 선언
        cht.SetSourceData refPivotTable.TableRange2
        
    ' Chart 생성
    With cht
        .ChartType = xlBarStacked   ' 새로 막대형
        .SetElement (msoElementChartTitleAboveChart) ' Title 추가
        .chartTitle.Text = chartTitle  ' Title text 추가
        .chartTitle.Left = 200         ' Resize
        .chartTitle.Top = 0.559
        .Axes(xlCategory).ReversePlotOrder = True
    End With
    
    ' Data Label 추가
    Dim Srs As Series
    For Each Srs In cht.SeriesCollection
        Srs.ApplyDataLabels
    Next
    
End Sub

Public Sub makeSlicer(ByRef refPivotTable As PivotTable, ByRef TitleName As String, ByRef SlicerLocation As Range)
    Dim shtPivot As Worksheet
    Set shtPivot = Workbooks(ThisWorkbook.Name).Sheets("PivotSheet")

    ' Slicer Cache 생성
    Dim slCache As SlicerCaches
    Set slCache = ThisWorkbook.SlicerCaches
    
    Dim slData As Slicers
    Dim slMod As Slicer
    Dim SlicerSourceField As String
    
    ' 원본으로 할 PivotTable로 Slicer 생성
    SlicerSourceField = TitleName
    Set slData = slCache.Add2(refPivotTable, SlicerSourceField).Slicers

    '# add(시트, , 데이터, Slicer Title)
    Set slMod = slData.Add(shtPivot, , TitleName, TitleName)

    Set rng = SlicerLocation
    slMod.Top = rng.Top
    slMod.Left = rng.Left

End Sub