[엑셀VBA] 피벗테이블 자동 업데이트(refresh)

피벗테이블 업데이트

피벗테이블은 데이터 테이블이 업데이트 될때마다, 수동으로 업데이트를 해줘야 한다. 귀찮기는 한데.. 죽어도 못쓸 정도는 아니지만, 생각난 김에, 간단한 스크립트 하나를 짜보자.

 

 

 

#1 worksheet change 코드 활용

엑셀VBA에서 기본으로 제공되는 기능 중, 시트가 업데이트될 경우, 동작을 설정할 수 있다.  data라는 데이터테이블을 기반으로 Sheet2, Sheet3 피벗테이블 시트가 있다고 하면, data 시트에서 컨텍스트 메뉴(마우스 우버튼)를 호출하면, 코드보기(View Code) 메뉴가 있다.

 

 

클릭하면 data 시트의 코드창으로 이동하게 되는데, 역기서 아래 코드를 입력해주면 된다. 또는 IDE 상단의 드롭다운 메뉴를 통해 Worksheet / Change에 대한 sub 프로시저를 생성하고, 원하는 코드를 입력해도 된다. 값이 업데이트 되는 경우, 모든 시트의 피벗테이블이 업데이트 된다.

시트가 조금이라도 업데이트 되면 무조건 전체 피벗테이블이 업데이트되므로, 시트가 많거나 데이터 수정량이 많아지는 경우, 느려지다가, 어느 수준을 넘어서게 되면 에러가 날 수 있다.

 

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim ws As Worksheet

  Dim pt As PivotTable

 

  For Each ws In ThisWorkbook.Worksheets

       For Each pt In ws.PivotTables

           pt.PivotCache.Refresh

       Next pt

  Next ws

End Sub

 

해당 코드는  파일이 켜져있는동안 유효할 것이고, 닫는 경우, 매크로 포함 파일로 저장하지 않으면 없어지게 된다. 그 말인 즉. 매번 귀찮게 넣어줘야 한다는 것이다.

 

 

 

#2 파일 외부에서 코드 컨트롤

원할때 코드를 넣었다가 뺏다가 해서, 스트레스를 줄여보도록 하자. 리본 메뉴 혹은 별도의 스크립트 add-in을 사용하고 있다면 적용해볼 법 하다.

나의 경우는, 자작으로 만든 매크로 add-in 파일에서 위의 스크립트를 Book1.xlsx 파일에 원하는 때만 넣어줄 예정이다.

미리 알아 둘 것은 파일명(여기서는 Book1.xlsx)와 데이터 테이블이 있어서 코드를 넣어야 하는 시트명(여기서는 data)이다.

 

 

 

① reference 활성화

우선 도구 - 참조(reference) 팝업창에서 Microsoft Visual Basic for Applications Extensibility 5.3 항목이 체크되어 있지 않다면, 활성화 해준다.

 

 

 

보안센터의 개발자 매크로 설정도 아래와 같이 수정을 해줘야 한다.

 

 

 

② 추가기능에서 코드 추가

원하는 추가기능의 모듈에서 아래 코드를 넣어준다.

 

Sub pivotRefresh()

    Dim wbname As String

    Dim datashtname As String

    Dim wb As Workbook

   

    Dim xPro As VBIDE.VBProject

    Dim xCom As VBIDE.VBComponent

    Dim xMod As VBIDE.CodeModule

    Dim xLine As Long

   

    On Error GoTo Err_Check

       

        wbname = Application.InputBox("set Target workbook name")

        datashtname = Application.InputBox("set data sheet name")

       

        Set wb = Workbooks(wbname)

       

        With wb

            Set xPro = .VBProject            

            Set xCom = xPro.VBComponents(Worksheets(datashtname).CodeName)

            Set xMod = xCom.CodeModule            

           

            If MsgBox("PivotTable Auto Update >> 생성 = YES, 삭제 = NO", vbYesNo) = vbYes Then

           

                    If xMod.Find("Change", 1, 1, 1, 1) = False Then

                        With xMod

                            xLine = .CreateEventProc("Change", "Worksheet")

                            xLine = xLine + 1

                            .InsertLines xLine, "  Next ws"

                            .InsertLines xLine, "       Next pt"

                            .InsertLines xLine, "           pt.PivotCache.Refresh"

               

                            .InsertLines xLine, "       For Each pt In ws.PivotTables"

                            .InsertLines xLine, "  For Each ws In ThisWorkbook.Worksheets"

                            .InsertLines xLine, "  Dim pt As PivotTable"

                            .InsertLines xLine, "  Dim ws As Worksheet"

                        End With

                    Else: GoTo Err_Check

                    End If            

            Else

                For n = xMod.CountOfLines To 1 Step -1

                    xMod.DeleteLines (n)

                Next n              

            End If

        End With    

   

Err_Check:

    If Err.Number <> 0 Then

        MsgBox "오류번호 : " & Err.Number & vbCr & _

        "오류내용 : " & Err.Description, vbCritical, "오류"

    End If

   

End Sub

 

 

③ 테스트

자, 이제 준비는 끝났고, 실행하면, 대상 파일명과 시트명을 기입하는 인풋박스가 차례로 나오고, 코드를 생성할 것인지 지울 것인지를 체크하는 YES/NO 항목이 나온다. 그럼 아래의 그림에서 대구의 딸기를 0으로 바꾸면, 

 

 

 

모든 시트의 피벗테이블에 반영된 것을 확인할 수 있다. 피벗테이블의 사용방법은 여러가지이므로, 각자 구미에 맞춰 코드를 수정하여 사용하면 되겠다.

 

 

 

사용을 멈출때는 동일 코드에서 위에서 말한 NO를 눌러 코드를 삭제해주면 되고,

최종 저장시에 매크로를 쓰지 않는 경우, 그냥 xlsx 파일로 저장을 해주면 된다. 물론 매크로 포함으로 저장시에는 해당 코드가 그대로 살아있을 것이다.

 

글로 적는데는 아무래도 한계가 있겠으나..

 

끝.