Tjyylli

Towers in the ocean; How deep can they be built?

Why don't programming languages automatically manage the synchronous/asynchronous problem?

Strange use of "whether ... than ..." in official text

Cannot shrink btrfs filesystem although there is still data and metadata space left : ERROR: unable to resize '/home': No space left on device

Is it okay to majorly distort historical facts while writing a fiction story?

How do I fit a non linear curve?

Is it convenient to ask the journal's editor for two additional days to complete a review?

The Ultimate Number Sequence Puzzle

What are the unusually-enlarged wing sections on this P-38 Lightning?

Is there a way to save my career from absolute disaster?

Raspberry pi 3 B with Ubuntu 18.04 server arm64: what chip

In the "Harry Potter and the Order of the Phoenix" video game, what potion is used to sabotage Umbridge's speakers?

What's the commands of Cisco query bgp neighbor table, bgp table and router table?

Is it professional to write unrelated content in an almost-empty email?

Computationally populating tables with probability data

Is it ok to trim down a tube patch?

Aggressive Under-Indexing and no data for missing index

What happened in Rome, when the western empire "fell"?

Do scriptures give a method to recognize a truly self-realized person/jivanmukta?

Can this note be analyzed as a non-chord tone?

Won the lottery - how do I keep the money?

Why do we say 'Un seul M' and not 'Une seule M' even though M is a "consonne"

My ex-girlfriend uses my Apple ID to login to her iPad, do I have to give her my Apple ID password to reset it?

Is it ever safe to open a suspicious HTML file (e.g. email attachment)?

Changing data source of pivot tables with VBA

0

I'm looking for a code that can change the data source of my pivot tables from one sheet with a different data source.
The data source sheet is identical just with different data.

I have a code that i've taken from the internet, but instead of changing the data source of my active sheet, it changes the data source on all my sheets.

Sadly i don't know much about VBA coding to know how to change the code to affect only my active sheet and i was hoping someone could help me.

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

Thank you in advance for the help!

microsoft-excel pivot-table

share|improve this question

asked 13 mins ago

Pop ClaudiuPop Claudiu

83

add a comment | 

0

I'm looking for a code that can change the data source of my pivot tables from one sheet with a different data source.
The data source sheet is identical just with different data.

I have a code that i've taken from the internet, but instead of changing the data source of my active sheet, it changes the data source on all my sheets.

Sadly i don't know much about VBA coding to know how to change the code to affect only my active sheet and i was hoping someone could help me.

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

Thank you in advance for the help!

microsoft-excel pivot-table

share|improve this question

asked 13 mins ago

Pop ClaudiuPop Claudiu

83

add a comment | 

I'm looking for a code that can change the data source of my pivot tables from one sheet with a different data source.
The data source sheet is identical just with different data.

I have a code that i've taken from the internet, but instead of changing the data source of my active sheet, it changes the data source on all my sheets.

Sadly i don't know much about VBA coding to know how to change the code to affect only my active sheet and i was hoping someone could help me.

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

Thank you in advance for the help!

microsoft-excel pivot-table

share|improve this question

asked 13 mins ago

Pop ClaudiuPop Claudiu

83

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

share|improve this question

asked 13 mins ago

Pop ClaudiuPop Claudiu

83

share|improve this question

asked 13 mins ago

Pop ClaudiuPop Claudiu

83

asked 13 mins ago

Pop ClaudiuPop Claudiu

83

asked 13 mins ago

Pop ClaudiuPop Claudiu

83