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

Vityata Vityata · Accepted Answer · 2018-05-28T08:02:50

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")

VBA pivot table data source error

1 Answers