Wijzigen van een Pivot Table naam met een macro

[DaviddeV1990]

Beste,

Ik zit al een hele tijd te zoeken naar een goede oplossing voor onderstaand probleem, maar ik kom er nog steeds niet uit. Het lijkt mij dat het echt mogelijk is en dat het niet heel moeilijk is voor iemand met verstand van macro's, maar ik heb de oplossing nog altijd niet gevonden (want ik heb ook niet veel verstand van macro's).

Het probleem is als volgt: Ergens op een server staat een bestand met diverse PivotTables. Deze PivotTables worden om de twee uur vernieuwd waardoor ze nieuwe data bevatten. Voor mijn Excel Dashboard heb ik gegevens nodig uit deze PivotTable. Ik heb er daarom voor gekozen om deze PivotTables te kopieren naar mijn (lokale) bestand. In de lokale versie wil ik vervolgens de PivotTable verkleinen door in het PivotFiel "Winkelafdeling" te kiezen voor (bijvoorbeeld) 'AGF'. Hier gaat het echter mis. Als ik de PivotTables kopieer dan geeft Excel deze zelf een naam omdat ik 2 hele sheets kopieer, waar toevallig twee PivotTables op staan. Eerst heten deze PivotTables "PivotTable1" en "PivotTable2" als ik vervolgens de PivotTables opnieuw inlaad, dan krijgen de nieuwe PivotTables de namen "PivotTable3" en "PivotTable4" en daar 5 en 6 etc. In de volgende macro wordt echter een bepaalde PivotTable aangeroepen, namelijk altijd "PivotTable1" en "PivotTable2". Ik krijg echter een error als ik de PivotTables opnieuw heb gekopieerd omdat "PivotTable1" en "PivotTable2" niet meer bestaan (dit zijn bijvoorbeeld "PivotTable3" en "PivotTable4" geworden). Hoe kan ik er voor zorgen dat in de tweede macro altijd de goede PivotTables worden aangeroepen? Wat moet ik hiervoor wijzigen in macro 1 of macro 2?

De macro's zijn hieronder toegevoegd:

Dit is de macro die de PivotTables moet kopieren:

Sub CopyPvts()

Dim ws As Worksheet
Dim wb As Workbook
Dim SheetList
Dim i As Long
SheetList = Array("Sheet1", "Sheet2") ' change to suit - these are the sheet names to copy TO and must exist
Set wb = Workbooks.Open("C:\Users\David\Documents\DraaiTabelGebruikAfprijzing2007.xlsx") ' change to suit
For Each ws In Worksheets(Array("AfprPerFilPerSubgrpPerArtGELD", "AfprPerFilPerSubgrpPerArtCE")) ' change to suit = sheet names to copy FROM
ThisWorkbook.Sheets(SheetList(i)).UsedRange.ClearContents
ws.UsedRange.Copy Destination:=ThisWorkbook.Sheets(SheetList(i)).Range("A1")
i = i + 1
Next ws
wb.Close savechanges:=False
End Sub

Dit is de macro die het juiste "PivotField" selecteert:

Sub PVTAfdelingKiezen()
'
' PVTAfdelingKiezen Macro
'
Sheets("AfprPerFilPerSubgrpPerArtCE").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Winkel afdeling"). _
        CurrentPage = Sheets("Admin").Range("B136").Text
    Sheets("AfprPerFilPerSubgrpPerArtGELD").Select
    ActiveSheet.PivotTables("PivotTabel2").PivotFields("Winkel afdeling"). _
        CurrentPage = Sheets("Admin").Range("B136").Text
    Sheets("AGF").Select
    Range("M32").Select
        
End Sub

Wie kan mij helpen om dit probleem op te lossen, want ik kom er echt niet uit.

Alvast enorm bedankt voor degene die mij het verlossende antwoord kan geven!

 

[HSV]

Misschien kun je hier iets mee.

Sub hsv()
With Sheets(1)
 For i = 1 To .PivotTables.Count
  .PivotTables(i).Name = "pivottable" & i
 Next i
End Sub

 

[DaviddeV1990]

Bedankt voor je reactie, moet ik dit in 1 van de bestaande macro's zetten (zo ja, waar?) of moet ik dit als een nieuwe macro toevoegen die wordt uitgevoerd na de eerste macro?

In ieder geval bedankt. Als dit werkt dan ben ik je eeuwig dankbaar!

 

[HSV]

Zodra de namen veranderd zijn, deze macro los uitvoeren.
Wel even testen in een testbestand.

 

[DaviddeV1990]

Zodra de namen veranderd zijn, deze macro los uitvoeren.
Wel even testen in een testbestand.

Het lijkt nu al deels te werken. Voor 'End Sub' moest ik nog wel 'End With' zetten volgens mij? Maar als ik nu de macro uitvoer, dan wijzigt alleen de naam van de PivotTable op Sheet1, de PivotTable op Sheet 2 blijft gewoon dezelfde naam hebben. Hoe los ik dit op?

 

[HSV]

De 'End With' was ik inderdaad vergeten.
De draaitabelnamen moeten doorlopen met tellen?, of elk blad van voor af aan met 1, 2, 3 enz.

 

[DaviddeV1990]

De 'End With' was ik inderdaad vergeten.
De draaitabelnamen moeten doorlopen met tellen?, of elk blad van voor af aan met 1, 2, 3 enz.

De draaitabel op Sheet1 moet altijd dezelfde naam hebben en de de draaitabel op Sheet2 moet ook altijd dezelfde naam hebben. Dus bijvoorbeeld altijd PivotTable1 en PivotTable2.

 

[HSV]

Sub hsv()
For sh = 1 To 2
With Sheets(sh)
  For i = 1 To .PivotTables.Count
   .PivotTables(i).Name = "pivottable" & i
    Next i
  End With
 Next sh
End Sub

 

[DaviddeV1990]

Nu wijzigt hij wel van beide PivotTables de naam, maar ze krijgen allebei de naam PivotTable1. Ik weet niet of dat consequenties heeft voor de tweede macro waarin ik een PivotField aanpas?

 

[HSV]

Dat vroeg ik, moet het tellen doorlopen?

 

[DaviddeV1990]

Dan begrepen we elkaar verkeerd denk ik. Excuses. De PivotTables moeten wel verschillende namen hebben, maar wel namen die steeds hetzelfde blijven. Dus de PivotTable op sheet1 moet altijd dezelfde naam hebben (bijvoorbeeld PivotTable1) en de PivotTable op sheet2 moet ook altijd dezelfde naam hebben (bijvoorbeeld PivotTable2).

 

[HSV]

Ik ga er nu van uit dat er bv. op elk blad 'twee' draaitabellen staan, maar maakt voor de code niet uit.
Draaitabellen op blad 1 krijgen de naam 'pivottable1' en 2, blad2 worden de namen resp. 'pivottable3' en 4.

Sub hsv()
For sh = 1 To 2
With Sheets(sh)
  For i = 1 To .PivotTables.Count
     y = y + 1
   .PivotTables(i).Name = "pivottable" & y
    Next i
  End With
 Next sh
End Sub

 

[DaviddeV1990]

Er staat maar 1 PivotTable per sheet. Maar het werkt nu in mijn testbestand! Ik zal morgen kijken of het ook werkt in mijn eigen bestand. Zo niet, dan laat ik wel weer wat van me horen. Maar ik ga er van uit dat het daar ook werkt. Voor nu zet ik het topic op opgelost. Heel erg bedankt in ieder geval voor het geven van de oplossing!

 

[HSV]

Dan kan er één lus uit.

Sub hsv()
For sh = 1 To 2
With Sheets(sh)
   .PivotTables(1).Name = "pivottable" & sh
  End With
 Next sh
End Sub

 

[DaviddeV1990]

Ik heb nu nog een kleine nieuwe vraag. Ik wil in de tweede macro ook dat het filiaal gekozen wordt. Dit filiaalnummer staat op de sheet 'Hoofdmenu' in cel D3. Ik dacht dat ik dan gewoon deze code kon toevoegen:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Filiaal"). _
        CurrentPage = Sheets("Hoofdmenu").Range("D3").Text

Dit werkt echter niet (ik krijg de melding "Unable to set the CurrentPage property of the PivotField class"). Wat doe ik fout? Het moet dus bij onderstaande macro erbij komen.

Sub PVTAfdelingKiezen()
'
' PVTAfdelingKiezen Macro
'
Sheets("AfprPerFilPerSubgrpPerArtCE").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Winkel afdeling"). _
        CurrentPage = Sheets("Admin").Range("B136").Text
    Sheets("AfprPerFilPerSubgrpPerArtGELD").Select
    ActiveSheet.PivotTables("PivotTabel2").PivotFields("Winkel afdeling"). _
        CurrentPage = Sheets("Admin").Range("B136").Text
    Sheets("AGF").Select
    Range("M32").Select
        
End Sub

Alvast bedankt voor de hulp!

 

[DaviddeV1990]

Iemand die mij met dit laatste vraagje kan helpen?

Enorm bedankt alvast.