피벗테이블을 VBA로 컨트롤 하고 싶으신 분들은 참고하세요.

updated 2010.02.24 22:09 by abyul.com

'피벗테이블의 행부분을 선택한다.
? ActiveSheet.PivotTables(1).RowRange.select

'피벗테이블의 열부분을 선택한다.
? ActiveSheet.PivotTables(1).ColumnRange.select

'피벗테이블의 페이지필드를 선택한다.
? ActiveSheet.PivotTables(1).PageRange.Select

'피벗테이블의 데이터 영역부분을 선택한다.
? ActiveSheet.PivotTables(1).DataBodyRange.select

'피벗테이블의 데이터필드들의 이름목록을 선택한다.
? ActiveSheet.PivotTables(1).DataLabelRange.select

'피벗테이블에서 페이지필드를 제외하고 모두 선택한다.
? ActiveSheet.PivotTables(1).TableRange1.Select

'피벗테이블에서 페이지필드를 포함해 전부 선택한다.
? ActiveSheet.PivotTables(1).TableRange2.Select

'피벗테이블 행부분이 있는 영역을 전부 선택
? ActiveSheet.PivotTables(1).TableRange1.Resize(ActiveSheet.PivotTables(1).ColumnRange.Rows.Count).Select

'아래 내용들은 본인이 직접 VBA창(Alt+F11)의 실행창(Ctrl+G)에서 확인해본 내용이다.
'그래서 변수값을 확인할때면.. 앞에 ? 가 붙는다. (?대신에 print나 debug.print를 해도 결과는 같다.)
'아래 내용의 확인 시간은 2008.12.19 00:24 AM 이다. 


 '피벗테이블의 행부분을 선택한다.
? ActiveSheet.PivotTables(1).RowRange.select
'피벗테이블의 행 항목 중 첫번째 항목의 이름을 갖고 온다.
? ActiveSheet.PivotTables(1).RowFields(1).name

'피벗테이블의 열부분을 선택한다.
? ActiveSheet.PivotTables(1).ColumnRange.select
'피벗테이블의 열 항목 중 첫번째 항목의 첫번째 아이템의 값을 갖고 온다.
? ActiveSheet.PivotTables(1).ColumnFields(1).PivotItems(1).value

'피벗테이블의 데이터 영역부분을 선택한다.
? ActiveSheet.PivotTables(1).DataBodyRange.select
'피벗테이블의 데이터 항목의 총 개수를 갖고 온다.
? ActiveSheet.PivotTables(1).DataFields.COUNT
'피벗테이블의 데이터 항목 중 2번째 항목의 이름을 갖고 온다.
? ActiveSheet.PivotTables(1).DataFields(2).name

'피벗테이블의 피벗필드의 개수를 갖고 온다.
'기본적으로 원본데이터의 필드명을 차례로 갖고 오고, 뒤쪽 배열엔 데이터 항목이 들어간다.
? ActiveSheet.PivotTables(1).PivotFields.count
'피벗테이블의 피벗필드 중 10번째 항목의 이름을 갖고 온다.
? ActiveSheet.PivotTables(1).PivotFields(10).name

'피벗테이블의 첫번째 데이터 필드 항목의 함수를 합계로 지정해준다.
ActiveSheet.PivotTables(1).DataFields(1).Function = xlSum
'피벗테이블의 첫번째 데이터 필드 항목의 서식을 숫자 서식으로 변경해준다.
ActiveSheet.PivotTables(1).DataFields(1).NumberFormat = "_-* #,##0_-;[RED]_-* -* #,##0_-;_-* -_-;_-@_-"

'행항목들의 총합계 부분이 있는지 여부를 확인할때..
? ActiveSheet.PivotTables(1).ColumnGrand
'열항목의 총합계의 이름을 갖고 올때.. 보통은 "총합계"
? ActiveSheet.PivotTables(1).GrandTotalName
'열의 총합계 부분을 선택하고 싶을때
ActiveSheet.PivotTables(1).PivotSelect "Column Grand Total", xlDataAndLabel, True
'선택한 부분합을 제거한다.
selection.delete
'다른 방법으로는 아래와 같이 하면, 부분합이 취소된다.
ActiveSheet.PivotTables(1).ColumnGrand = False
'다시 열의 총합계를 설정하는 방법은 아래와 같다.
ActiveSheet.PivotTables(1).ColumnGrand = True

'행항목의 부분합이 설정되어 있는지 확인하는 방법은 아래와 같다.
? ActiveSheet.PivotTables(1).PivotFields(1).Subtotals(1)
'행항목의 부분합 부분을 선택한다.
ActiveSheet.PivotTables(1).PivotSelect ActiveSheet.PivotTables(1).RowFields(1).name + "[All;Total]", xlDataAndLabel, True
'선택한 부분합을 제거한다.
selection.delete
'다른 방법으로는 아래와 같이 하면, 부분합이 취소된다.
ActiveSheet.PivotTables(1).PivotFields(1).Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
'좀더 간단하게 부분합을 제거하는 방법은 아래와 같다.
ActiveSheet.PivotTables(1).PivotFields(1).Subtotals(1) = False
'참고로, 다시 부분합을 설정하는 방법은 아래와 같다.
ActiveSheet.PivotTables(1).PivotFields(1).Subtotals = Array(True, False, False, False, False, False, False, False, False, False, False, False)

'위 양식은 아래와 같은 의미임. 첫번째엔 True가 있어야 하고.. 나머지는 필요에 따라서.. 합계나 개수, 평균등 입맛에 맞게 True로 설정해주면 된다.
ActiveSheet.PivotTables(1).PivotFields(1).Subtotals = Array(자동합계여부,합계,개수,평균,최대값,최소값,곱,숫자 개수,표본 표준 편차,표준 편차,표본 분산,분산)


'피벗테이블의 행레이블과 데이터 영역까지 선택하기.. 이게 영역 설정시 가장 많이 사용할 듯..
Range(ActiveSheet.PivotTables(1).DataBodyRange, ActiveSheet.PivotTables(1).RowRange).select
'열 첫번째 항목의 레이블과 데이터 영역을 모두 선택하기
ActiveSheet.PivotTables(1).PivotSelect ActiveSheet.PivotTables(1).ColumnFields(1).Name + "[All]", xlDataAndLabel, True
'열 첫번째 항목의 레이블만 선택하기
ActiveSheet.PivotTables(1).PivotSelect ActiveSheet.PivotTables(1).ColumnFields(1).Name + "[All]", xlLabelOnly, True
'열 첫번째 항목의 데이터 영역만 선택하기
ActiveSheet.PivotTables(1).PivotSelect ActiveSheet.PivotTables(1).ColumnFields(1).Name + "[All]", xlDataOnly, True
'행 첫번째 항목의 레이블과 데이터 영역을 모두 선택하기
ActiveSheet.PivotTables(1).PivotSelect ActiveSheet.PivotTables(1).RowFields(1).Name + "[All]", xlDataAndLabel, True

'행필드를 제거하기
ActiveSheet.PivotTables(1).rowFields(1).Orientation = xlHidden
'열필드를 제거하기
ActiveSheet.PivotTables(1).columnFields(1).Orientation = xlHidden
'데이터필드를 제거하기
ActiveSheet.PivotTables(1).dataFields(1).Orientation = xlHidden

'데이터 필드에 "입고"필드를 합계로 만들어서 추가하기
ActiveSheet.PivotTables(1).AddColumnField ActiveSheet.PivotTables(1).PivotFields("입고"), " 입고", xlSum
'행필드에 "입고"필드 추가하기
ActiveSheet.PivotTables(1).PivotFields("입고").Orientation = xlRowField
'열필드에 "입고"필드 추가하기
ActiveSheet.PivotTables(1).PivotFields("입고").Orientation = xlColumnField





데이터 영역의 필드명과 열 컬럼의 레이블 부분만 선택하는 코드..
ActiveSheet.PivotTables(1).PivotSelect ActiveSheet.PivotTables(1).ColumnFields(ActiveSheet.PivotTables(1).ColumnFields.count).Name + "[All]", xlDataAndLabel, True

ActiveSheet.PivotTables(1)을 p로 대체하면 아래와 같다.

Dim p as PivotTable
set p = ActiveSheet.PivotTables(1)
p.PivotSelect p.ColumnFields(p.ColumnFields.count).Name + "[All]", xlDataAndLabel, True