Pivot VBA (피벗 VBA)

Pivot 을 엑셀에서 일일이 매번 지정하려니까 너무 귀찮아서 피벗 VBA 를 아예 만들었다.

어떻게 만드는지 감이 오지 않으면 매크로를 실행해서 만들어진 코드를 분석하면 쉽게 이해할 수 있다.

Sub pivot_make()
    Dim endRow As Long
    Dim CurrentPosition
    Dim C As Range
    Dim rngAll As Range
    Dim wSheet As Worksheet
    Dim wkSht As Worksheet

    On Error Resume Next
    Set wkSht = ThisWorkbook.Worksheets("B_Sheet")
    If Err.Number = 0 Then
        ' Application.DisplayAlerts = False
        ' Worksheets("B_Sheet").Delete
        ' Application.DisplayAlerts = True
    Else
    Worksheets("A_Sheet").Activate
    Worksheets("A_Sheet").Copy after:=Worksheets("A_Sheet")
    ActiveSheet.Name = "B_Sheet"
    End If

    Sheets("B_Sheet").Select
    endRow = Cells(Rows.Count, "A").End(3).Row '// 셀의 마지막 위치가 계속 변하므로

    Cells(1, 1).Select
    If Cells(3, 1) <> "등록일" Then
        Selection.EntireRow.Insert
        Selection.EntireRow.Insert
    End If
    Cells(3, 1).Select
    ActiveSheet.AutoFilterMode = False
    If ActiveSheet.AutoFilterMode = False Then
        Selection.AutoFilter
    End If
    Cells(3, "C").Select

    ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveSheet.AutoFilter.Sort.SortFields.Add Key:= _
    Range("C3:C" & endRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveCell.Select
    Cells(3, "C").Select

    '###### 분류 작업 #######
    Set rngAll = Range([C3], Cells(Rows.Count, "C").End(3))

    Application.ScreenUpdating = False
    rngAll.Replace "회사", vbNullString
    rngAll.Offset(0, 1).Replace "기술부", vbNullString

    '##### 피벗테이블 생성 #####
    Sheets("B_Sheet").Select

    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Pivot_New").Delete '// 기존 피벗테이블 삭제
    Sheets.Add(after:=Sheets(8)).Name = "Pivot_New" '새로운 피벗시트를 생성

    Set rngData = Sheets("B_Sheet").Range("A3").CurrentRegion
    Set rngB = ActiveWorkbook.Worksheets("Pivot_New").Range("A3")

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, _
    Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=rngB, _
    TableName:="PVR", DefaultVersion:=xlPivotTableVersion14

    '// 보고서 필터
    With ActiveSheet.PivotTables("PVR").PivotFields("부서")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PVR").PivotFields("지역")
        .Orientation = xlPageField
        .Position = 2
    End With

    '// X축
    With ActiveSheet.PivotTables("PVR").PivotFields("분류")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PVR").PivotFields("고객")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PVR").PivotFields("분류").LayoutForm = xlTabular
    ActiveSheet.PivotTables("PVR").PivotFields("고객").LayoutForm = xlTabular

    '// Y축
    With ActiveSheet.PivotTables("PVR").PivotFields("팀")
        .Orientation = xlColumnField
        .Position = 1
    End With

    '// 화면에 표시될 값(결과)
    ActiveSheet.PivotTables("PVR").AddDataField ActiveSheet.PivotTables( _
    "PVR").PivotFields("고객수"), "합계 : 고객수", xlSum

    '// 화면에서 보이지 않게 처리
    ActiveSheet.PivotTables("PVR").PivotFields("지역").CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PVR").PivotFields("지역")
        .PivotItems("서울").Visible = False
        .PivotItems("대구").Visible = False
        .PivotItems("부산").Visible = False
        .PivotItems("전남").Visible = False
        .PivotItems("전북").Visible = False
        .PivotItems("충남").Visible = False
        .PivotItems("충북").Visible = False
        .PivotItems("제주").Visible = False
        .PivotItems("강원").Visible = False
    End With

    ActiveSheet.PivotTables("PVR").PivotFields("담당").EnableMultiplePageItems = True

    MsgBox "피벗 생성완료"
End Sub