VBA pivot table data source error

Question

Good morning everyone !

I've searched through this forum but not found an answer to my problem with VBA and pivot tables.

What I try to do is quite simple. I have a workbook with two sheets, one containing data and the other pivot tables based on the data in the first sheet. I want to duplicate these two sheets (no problem here), then move the data source of the new pivot tables from the old data sheet to to the new one. When I run the code below, I get an Error 5. Any Idea on how to solve this ? Thank you very much :)

Sub nouvelle_periode()
   'Crée un nouveau planning vierge fonctionnel à partir des feuilles types

   'Copie les tableau et analyse
   ActiveWorkbook.Worksheets("Période type").Copy After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "nouvelle-periode"
   ActiveWorkbook.Worksheets("Analyse type").Copy After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "nouvelle-analyse"

   'Associer la bonne période comme référence pour les tableaux croisés 
   dynamiques de la feuille d'analyse
   Dim wb As Workbook
   Dim ws As Worksheet
   Dim pt As PivotTable
   Dim datarange As Range

   donnees = Worksheets("nouvelle-periode").Range("F10:M500")

   Worksheets("nouvelle-analyse").Activate
   Set wb = ActiveWorkbook
   Set ws = ActiveSheet

   For Each pt In ws.PivotTables
       pt.ChangePivotCache _
           wb.PivotCaches.Create(SourceType:=xlDatabase, _
               SourceData:=donnees)
   Next pt

End Sub

Add set donnees=Worksheets("nouvelle-periode").Range("F10:M500") now it will work fine — Aravindhan R
– Aravindhan R, Commented May 29, 2018 at 8:12

Vityata · Accepted Answer · 2018-05-28 08:02:50Z

1

donnees is a range object and objects are assigned with the word Set. It is is a good practice to declare the object as well, e.g. Dim donnees As Range somewhere before assigning it.

Set donnees = Worksheets("nouvelle-periode").Range("F10:M500")

answered May 28, 2018 at 8:02

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA pivot table data source error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related