☰

VBA를 사용하여 피벗 테이블의 데이터 소스 변경

tag-icon • tag-icon microsoft-excel pivot-table
VBA를 사용하여 피벗 테이블의 데이터 소스 변경

다른 데이터 소스가 포함된 한 시트에서 피벗 테이블의 데이터 소스를 변경할 수 있는 코드를 찾고 있습니다. 데이터 원본 시트는 데이터만 다를 뿐 동일합니다.

인터넷에서 가져온 코드가 있는데 활성 시트의 데이터 소스를 변경하는 대신 모든 시트의 데이터 소스가 변경됩니다.

안타깝게도 나는 내 활성 시트에만 영향을 미치도록 코드를 변경하는 방법을 알기 위해 VBA 코딩에 대해 잘 모르고 누군가가 나를 도울 수 있기를 바랐습니다.

Option Explicit

Sub ChangeDataSourceForAllPivotTables()

    Dim wb                  As Workbook
    Dim ws                  As Worksheet
    Dim pt                  As PivotTable
    Dim rSourceData         As Range

    If ActiveWorkbook Is Nothing Then Exit Sub

    On Error GoTo ErrHandler

    Set wb = ActiveWorkbook

    Set rSourceData = wb.Worksheets("Sheet1").Range("A1").CurrentRegion 'change the name of the worksheet accordingly

    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            pt.ChangePivotCache wb.PivotCaches.Create(xlDatabase, rSourceData.Address(, , , True))
            pt.RefreshTable
        Next pt
    Next ws

ExitTheSub:
    Set wb = Nothing
    Set ws = Nothing
    Set pt = Nothing
    Set rSourceData = Nothing

    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ":  " & Err.Description, vbCritical, "Error"
    Resume ExitTheSub

End Sub

도움을 주셔서 미리 감사드립니다!

답변1

이 코드는 특정 시트의 모든 피벗 테이블에 대한 소스 데이터를 변경하는 데 도움이 됩니다.

Sub ChangePivotSourceData()

Dim pt As PivotTable

For Each pt In ActiveWorkbook.Worksheets("Sheet1").PivotTables
         pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:="MyData")
Next pt

End Sub

작동 방식:

  • Copy& Paste이 코드는 Standard Module.
  • Sheet이름 은 Source Data편집 가능합니다.
  • 이 코드에서는마이데이터는명명된 범위, 그 전에 만들어야 하는달리다 변경하고 싶을 때마다 이 코드피벗 테이블의 소스 데이터.

답변2

예를 들어 문서를 대상별로 분할하고 해당 피벗 차트를 사용하여 대상별 다양한 특정 파일을 자동으로 생성하고 싶습니다. 매번 새로운 피벗 차트의 데이터 소스를 업데이트하려면 어떻게 해야 합니까(스페인, 이탈리아, 프랑스...)? 모든 대상(유럽)의 원본 데이터 소스를 유지합니다. 감사해요

Dim pt As PivotTable
    Dim MyData As Excel.ListObject
   
    Set MyData = Application.Range(Europe).ListObject
   
For Each pt In ActiveWorkbook.Worksheets("Chart - City").PivotTables
         pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:="MyData")
Next pt

관련 정보