CODEWITHSUNDEEP
vbaexcelpivot-table
Dora
Dora

Reputation: 33

VBA pivot table data source error

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

Upvotes: 3

Views: 316

Answers (1)

Vityata
Vityata

Reputation: 43575

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

Upvotes: 1

Related Questions