개요

저번 포스팅에 이어 다른 보고서를 VBA로 자동화해보았다. 이번 보고서는 저번 보고서 다음으로 가장 복잡하기에 자동화를 도전해보게 되었다.

1. 피벗테이블 만들기

피벗테이블을 만드는 과정을 처음으로 자동화해보았다. 내가 해야 하는 일은 A시트로 피벗테이블을 생성하고 열과 값 필드를 설정하는 것이었다.

VBA로 피벗테이블을 만들 때 피벗캐시라는 것을 생성해야 피벗테이블을 만들 수 있다고 한다.

참고 : https://learn.microsoft.com/en-us/office/vba/api/excel.pivotcaches.create

	' 피벗테이블 만들 때 필요한 변수 선언
    Dim ptCache As PivotCache
    Dim wspivot As Worksheet
    Dim pt As PivotTable
    Dim destrange As Range

    ' wspivot 변수에 피벗테이블 시트를 할당
    Set wspivot = ThisWorkbook.Sheets("피벗")

    ' 첫번째 피벗테이블 생성
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets("A시트").Range("A1").CurrentRegion, Version:=xlPivotTableVersion15)
    Set destrange = wspivot.Range("A3")
    Set pt = ptCache.CreatePivotTable(tabledestination:=destrange, TableName:="pivotTable1")

    ' 피벗테이블 필드 설정(순서대로 열, 값 필드를 설정)
    With pt
        .PivotFields("팀").Orientation = xlRowField
        .PivotFields("ID").Orientation = xlDataField
    End With

2. 상대적 위치 설정하기

하나의 시트에 피벗테이블을 세개 만드는데, 첫번째 피벗테이블의 위치는 A3이다. 그리고 두번째 피벗테이블은 첫번째 피벗테이블보다 2행 아래에 있고 세번째 피벗테이블도 두번째 피벗테이블보다 2행 아래에 있다.

그래서 두번째, 세번째 피벗테이블을 만들 때는 첫번째 피벗테이블처럼 위치를 "A3" 이런식으로 하드코딩할 수 없었다.

pt.TableRange2.Offset(pt.TableRange2.Rows.Count + 2, 0)

• pt.TableRange2 : 피벗 테이블의 전체 범위를 참조
• pt.TableRange2.Rows.Count + 2 : 피벗테이블의 행의 수 + 2
• Offset(행, 열) : 피벗테이블의 행의 수에서 2 떨어진 행과 열(변경되지 않음)을 반환

이 위치를 두번째 피벗테이블을 만들 때의 위치로 넘겨줌으로써 상대적인 위치를 잡을 수 있었다.

	' 두번째 피벗테이블 생성
	Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets("A시트").Range("A1").CurrentRegion, Version:=xlPivotTableVersion15)

 	Set destrange = pt.TableRange2.Offset(pt.TableRange2.Rows.Count + 2, 0)

	Set pt = ptCache.CreatePivotTable(tabledestination:=destrange, TableName:="pivotTable2")

3. 시트 설정하기

모든 코드를 아래와 같이 A시트 안에서 코드를 실행하는 식으로 적었는데, 왜인지 잘 작동하지 않았다. 예를 들어 나는 A 시트의 a1을 선택하려고 했는데, 현재 활성화된 시트(B시트)의 a1셀이 선택되는 식이었다.

With Worksheets("A시트")
...
End With

그래서 임시방편으로 강제로 A시트를 활성화해서 그 시트에서 코드를 실행할 수 있게끔 바꿔줬다. 코드가 위에서부터 아래로 실행되고, 흐름이 바뀔 일이 없어서 이렇게 하는 게 가능했던 것 같다.

If ActiveSheet.Name <> Sheets("A시트").Name Then
	Sheets("A시트").Activate
End if

성과

기존에는 10분 30초 걸리던 작업이 7초로 단축되었다!

상사님께서 나는 몇달간 이걸 해왔기에 자동화 전에도 10분만 걸리는 거지 처음 해보는 사람은 한시간 넘게 걸릴 거라고, 그래서 시간이 엄청 단축될 거라고 말씀해주셔서 뿌듯했다.

다음에는?

VBA는 함수 스코프가 아니라서 With문 안에서 지역변수를 만들 수 없어 많이 아쉬웠다. 그래서 하나의 함수 안에서 변수를 여러개 만드려고 하니 마지막 열을 뜻하는 lastRow라는 변수가 lastRow1~6까지 생기는 등 보기에 좋지 않았다.

이런 문제를 어떻게 해결할 수 있을지 생각해보려고 한다. 그리고 다음에 다른 자동화 작업을 할 때 코드를 조립해서 쓸 수 있도록 공통된 코드를 분리하는 방법을 생각해보려고 한다.