Draaitabel in macro

[radar]

Excellenties,

Enige tijd geleden heb ik vraag geplaatst inzake sorteermacro.
N.a.v. tip Gert Bouwmeester terechtgekomen op site van Finch:
macro bleek niet nodig, binnen mogelijkheden draaitabel kon mijn vraag opgelost worden.
Ik heb nu deze draaitabeloptie ( het verdelen van de draaitabel over verschillende tabbladen op basis van criterium paginaveld) verwerkt in macro.
Nadat alle gegevens verwerkt zijn, wordt middels andere macro een aantal tabbladen verwijderd en een aantal leeggemaakt.
Als ik op enig moment weer macro uitvoer krijg ik foutmelding in regel waarin ik iets doe met tabblad waarin totaaltabel staat.
Het tabblad krijgt namelijk bij elke keer dat ik draaitabel maak een nieuw nummer.
In macro staat het oorspronkelijk nummer van het tabblad.
Hoe kan ik dit probleem tackelen?

Radar

 

[Finch]

Excellenties,

Enige tijd geleden heb ik vraag geplaatst inzake sorteermacro.
N.a.v. tip Gert Bouwmeester terechtgekomen op site van Finch:
macro bleek niet nodig, binnen mogelijkheden draaitabel kon mijn vraag opgelost worden.
Ik heb nu deze draaitabeloptie ( het verdelen van de draaitabel over verschillende tabbladen op basis van criterium paginaveld) verwerkt in macro.
Nadat alle gegevens verwerkt zijn, wordt middels andere macro een aantal tabbladen verwijderd en een aantal leeggemaakt.
Als ik op enig moment weer macro uitvoer krijg ik foutmelding in regel waarin ik iets doe met tabblad waarin totaaltabel staat.
Het tabblad krijgt namelijk bij elke keer dat ik draaitabel maak een nieuw nummer.
In macro staat het oorspronkelijk nummer van het tabblad.
Hoe kan ik dit probleem tackelen?

Radar

Kan je een voorbeeldbestand posten, of ten minste de code?
Misschien met de naam werken, ipv met het indexnummer?

 

[radar]

Hallo Finch,

Optie met naam werken, lukt ook niet omdat ik bij het toewijzen van naam ook een opdrachtregel in macro krijg waaringemakshalve even in gewoon nederlands) "tabblad met nummer" wordt "naam".
Ik maak even een nieuw sheet met macro's, met andere gegevens en zal dit zo meteen posten.

Radar

Voorbeeldbestandje toegevoegd.
Enige uitleg: blad 1 staan de basisgegevens waarmee ik draaitabel maak; in voorbeeld heb ik hele procedure al een keer doorlopen, waardoor de huidige draaitabel niet meer op blad 4 staat, doch op blad 10, terwijl in macro blad 4 staat en dus foutmelding geeft.

Radar

 

[Finch]

Staan er in je bestand nog andere draaitabellen buiten deze?

 

[radar]

Hallo Finch,

Het gaat om een draaitabel van bestand met zo'n 3000 regels, met uiteindelijk ongeveer zo'n 50 tabbladen, welke elk afzonderlijk wordt uitgezet in organisatie, met geheel andere vertrouwelijke informatie.
Het is de bedoeling dat na verwerken van gegevens door derden, dit bestand periodiek weer wordt ververst met nieuwe gegevens, met vervolgens draaitabel en verdeling over diverse tabbladen.
In de originele format, dienen er enkele tabbladen te blijven staan en een aantal tabbladen dienen te worden vervangen.
Het is niet zo dat altijd dezelfde bladen uit draaitabel komen, noch qua naam, noch qua aantal.

Radar

 

[jzeedijk]

Hoi Radar,

Sheet(1) = Sheet("aken")
Dus Sheet("Blad10") is Sheet(7) (even uit mijn hoofd).
Dat zou je kunnen doen, alleen dan wordt altijd het 7e sheet verwijderd. Dat wil je misschien ook niet.

Je zou Sheet("Bladxx") ook tijdelijk Sheet("Tot") of Sheet("Tijdelijk") kunnen hernoemen en dat sheet keer op keer verwijderen (indien aanwezig).
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
Activesheet.name="Tijdelijk"
ActiveSheet.Cells(3, 1).Select
En in de macro voor verwijderen:
Sheet("tijdelijk").delete
Of zoiets
Het toegevegde sheet heet dus altijd tijdelijk en kan op die manier verwijderd worden.

Jeroen

 

[Finch]

Hallo Finch,

Het is niet zo dat altijd dezelfde bladen uit draaitabel komen, noch qua naam, noch qua aantal.

Radar

Dat dacht ik al toen ik je eerste post las Maar dat is ook geen overkomelijk probleem. Ik heb je macro's herschreven met in het achterhoofd hoe ik je vraag begrepen heb.

Gebruik deze code eens voor je sorteermacro:

Sub Sorteer()
Dim oPT As PivotTable
Dim oPC As PivotCache
Dim wsPt As Worksheet
Set wsPt = Worksheets.Add
Set oPC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Blad1!R2C2:R8C5")
        
Set oPT = oPC.CreatePivotTable(TableDestination:=wsPt.Range("A3"), TableName:="PTPlaatsnamen")

oPT.AddFields Array("Plaatsnamen", "Kleuren")
oPT.PivotFields("cijfers").Orientation = xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
With oPT.PivotFields("Plaatsnamen")
    .Orientation = xlPageField
    .Position = 1
End With
oPT.ShowPages "Plaatsnamen"
        
End Sub

en deze voor je verwijderen macro (ook de functie mee kopiëren):

Sub Deleten()
Dim oPT As PivotTable
Dim oPTloop As PivotTable
Dim oPI As PivotItem
Dim ws As Worksheet

For Each ws In Worksheets
    For Each oPTloop In ws.PivotTables
        If oPTloop.Name = "PTPlaatsnamen" Then
            Set oPT = oPTloop
        End If
    Next oPTloop
Next ws

Application.DisplayAlerts = False
If Not oPT Is Nothing Then
    For Each oPI In oPT.PageFields("Plaatsnamen").PivotItems
        If SheetExists(oPI.Value) Then
            Sheets(oPI.Value).Delete
        End If
    Next oPI
    Sheets(oPT.Parent.Name).Delete
End If
Application.DisplayAlerts = True
End Sub

Function SheetExists(SheetName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(SheetName)
If Err > 0 Then
   SheetExists = False
   Err.Clear
Else
   SheetExists = True
End If

End Function

Je moet de verwijder macro wel runnen alvorens je nieuwe gegevens gaat toevoegen en de sorteermacro gaat gebruiken, anders gaan niet alle tabbladen verwijderd worden.

In mensentaal wat er in de macro verwijderen (deleten) gebeurt:

Zoek eerst de PT met als naam PTPlaatsnamen (naam komt van de sorteermacro).
loop in deze PT doorheen alle items van het veld plaatsnamen
indien er een sheet bestaat met een naam gelijk aan deze van een plaatsnaam item, verwijder die dan
Verwijder als laatste de sheet waar de PT staat

Ik hoop dat je er wat aan hebt.

Groeten,

Finch

 

[radar]

Hallo Finch,

Ik ga hiermee aan de slag, echter dat lukt me pas aan einde van de dag; ik meld me later weer.

Hoe dan ook, bedankt voor de aangedragen oplossing.:thumb:

@Jeroen,
Ook jij bedankt voor reactie; het gaat echter niet altijd om zelfde tabbladnummer en ook het wijzigen van de naam van het tabblad zal in macro gebeuren middels eerst een selectie van het desbetreffend tabblad op basis van originele naam.
Deze laatste verspringt iedere keer als procedure geheel doorlopen is.

groet,
Radar.

 

[radar]

Hallo Finch,

Door privé-omstandigheden heeft reactie wat op zich laten wachten.
Macro doet precies dat wat ik wil in voorbeeld; ga macro verwerken in het bestand waarvoor hij eigenlijk bedoeld is, in de macro die er omheen gebouwd is.

Dank je wel voor deze oplossing:thumb: