Office: (Office 2013) Pivottabelle Dateschnitt Refresh Blattschutz

Hallo zusammen,
ich habe ein Problem welches ich seit Tagen vergeblich versuch zu lösen. Vorab, ich bin kein Excel-Experte *wink.gif*.

Folgendes Scenario:
Habe 2 Tabellenblätter. Im 1ten sind die Daten, im 2ten die Pivottabellen (untereinander, und mit genug Platz das sie sich nicht überschneiden).
Es handelt sich dabei um Umsatzwerte, die mit Hilfe der Pivottabellen (in Sheet 2) schön gefiltert und tabellarisch aufgestellt werden.

Alles klappt bestens, jedoch liegt mein Problem darin, das diese Tabelle verschiedene Personen für die jeweilige Region bearbeiten müssen. Damit kein Unfug betrieben wird möchte ich das Sheet mit den Pivottabellen soweit wie es geht schützen. Auf dem Sheet habe ich dann noch Datenschnitte zum Filtern hinzugefügt, sodass man dann noch die Umsatztabellen filtern kann, beispielweise Monatsweise. Der Benutzer darf die erste Tabelle mit Daten füttern, bei der zweiten dann eigentlich nichts bis auf "refresh" und den jeweiligen Monat auswählen bzw. mit den Datenschnitten filtern.

Nun zum Problem welches ich nicht in der Kombination lösen kann:
Habe im Forum diverse Sachen ausprobiert ich schaffe es nicht, das ich den Refresh Button:

Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Sheets("Sheet2").Select
ActiveSheet.PivotTables("PivotTable2").RefreshTable

einfüge, dann mit folgender Funktion

Sub PT_aktualisieren()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
If ws.PivotTables.Count Then
ws.Unprotect "pw" 'Passwort anpassen
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect "pw" 'Passwort anpassen
End If
Next
End Sub

sozusagen den Blattschutz aufhebe und wieder einfüge

UND das ich weiterhin den Datenschnitt auswählen kann. Aktuell macht der Button was er soll, er aktualisierst ein passwortgeschützes Sheet, eben die mit den Pivottabellen, sobald man aber auf den Button klickt blockiert er mir auch den Bereich der Datenschnitte und kann da beispielsweise nicht mehr nach dem Monat filtern. Dann kommt die Fehlermeldung "PivotTable kann in schreibgeschützten Blatt nicht bearbeitet werden".

Der Datenschnitt-Schutz ist deaktiviert und bei der Auswahl beim Blattschützen, wähle ich "nicht gesperrte Zellen auswählen" "PivotTable und PivotChart verwenden" und "Objekte bearbeiten".

Da ich noch nie mit Makros gearbeitet habe, habe ich die ganzen Codes aus dem netz kopiert und dann eben für meine Tabellen/Sheets/Pivots angepasst.

Kann mir hier jemand helfen? Hoffe ich habe es einigermaßen gut beschreiben können. Beispielsweise muss kein RefreshButton verfügbar sein, wenn es geht, dass aus der Data-Tabelle die Folge Tabelle automatisch aktualisiert wird, heißt datenschnitt das dieser durch den monat ergänzt wird und man diesen dann auswählen kann. Die Automatische Aktualisierung habe ich mit folgendem versucht, jedoch nicht geklappt:

Private Sub Worksheet_Calculate()

'If data on this worksheet changes, refresh the pivot table
Sheets("Pivot").PivotTables("PivotTable1").RefreshTable

End Sub


Kurze Beschreibung evtl gibt es auch eine andere Lösung:
Tabelle 1: Daten in spalten und zeilenweise die frei vom Benutzer (mit Hilfe von Datenauswahl Dropdown) befüllt werden darf.
Tabelle 2: Mehrere Pivottabellen, die nach den jeweiligen Datenschnittfiltern ausgewählt werden dürfen, aktualisiert, wenn die Daten in Tabelle 1 eingetragen werden, jedoch darf die bearbeitende Person NICHTS bis auf die Benutzung der Datenschnittflächen in TAbelle 2 machen dürfen, also Passwort geschützt.

:)

 

Ach ja, ich vergaß zu erwähnen, das in der ersten Tabelle ich Namensmanager die Tabelle mit einer "Bereichsverschiebenfunktion" habe, sodass wenn man eben einen neuen Monat einträgt dieser in der nächsten dann automatisch erkannt wird.

 

1. Es ist nicht nötig alle Pivots zu refreshen, da sie höchstwahrscheinlich alle
auf die selbe Tabelle zugreifen und auch auf den selben Pivot-Cache.

Es langt den PivotCache z.B. der 1. Pivot zu refreshen.

Bsp mit Blattschutz Aufheben und wieder Setzen

Code:

Das Refreshen könntest du beim aktivieren der Tabelle2 durchführen lassen.
(Stichwort: Activate-Ereignis der Tabelle)

2. Der Datenschnitt lässt sich nur bedienen wenn er bei geschütztem Blatt
ungeschützt (also bei [Gesperrt] Haken entfern) ist.
Nachteil: Datenschnitt lässt sich löschen
Einmal gelöscht lässt sich, wenn zwischendurch gerefresht wurde, die Löschung nicht mehr rückgängig machen.

Datenschnitt muss dann per Code neu gesetzt werden. Das könnte auch bei Aktivierung von Tabelle2 erfolgen.

3. Statt mit Bereichnamen zu arbeiten, die über Formel die Größe ermittelt, wandele deine Tabelle in ein Tabellen-Objekt um
und Verweise deine Pivots da drauf.

 

Genau, die Pivots greifen alle auf die selbe Tabelle zu.

Wie beschrieben bin ich kein experte, sondern alles mehr über Tutorials und Foren versucht zu erstellen.

Zu 1. Muss ich schauen wie das activate-ereignis funktioniert. Bzw gibt es einen Code, das sobald die Daten eingetragen wurden für einen Monat, diese dann in der Pivot automatisch übernimmt, ohne ein Refresh Button?

Zu 2. Gibt es eine andere Möglichkeit, das die Person dann die Monate nur auwählen kann im Datenschnitt, also nur auf die einzelnen Monate klicken kann jedoch nichts anderes? Gäbe es evtl eine andere Möglichkeit?

Zu 3. Habe mit dem Bereichsnamen gearbeitet, da ich das als einzigsten online gefunden habe, wie man die Pivottabellen automatisch mit Daten ergänzt werden, auch wenn diese noch nicht eingetragen wurden. Das müsste ich mir dann auch nochmal anschauen wie das mit Tabellen-Objekt geht. Wobei es so auch funktioniert, außer es behindert die Lösung meines Problems.

 

Update: Habe die Autorefresh funktion erfolgreich in die Tabelle eingefügt,
jedoch dauert das "aktualisieren" 10sek pro feld, somit kann das ausgeschlossen werden, denn dann dauert es einfach zu lange 14 spalten á ca. 50 zeilen einzutragen.

mit folgendem code, sehr wahrscheinlich zu umständlich

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sales Overview").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("Sales Overview").PivotTables("PivotTable2").PivotCache.Refresh
Worksheets("Sales Overview").PivotTables("PivotTable3").PivotCache.Refresh
Worksheets("Sales Overview").PivotTables("PivotTable4").PivotCache.Refresh
Worksheets("Sales Overview").PivotTables("PivotTable5").PivotCache.Refresh
Worksheets("Sales Overview").PivotTables("PivotTable6").PivotCache.Refresh
End Sub

 

ich wiederhole...

 

Danke! dauert jetzt auch nicht mehr so lange *wink.gif*.

Muss die Tabelle als xlsm gespeichert werden, oder kann es auch ganz normal als xlsx gespeichert werden?

Diese Tabellen sollen dann wirklich von richtigen Anfängern ausgefüllt werden.

 

Bin fast am Ziel, ich würde dann diese Auto-Refresh Funktion nutzen, sodass diese dann automatisch die Monate in den Datenschnitt einfügt, was auch soweit klappt, aber wie und wo baue ich den Code bzgl. Passwortschutz ein.
Kommt das auf die erste Tabelle unter den code des autorefresh?!

Bei Hinterlegung des Passworts kommt in der Haupttabelle bei der Auswahl des Monats dann der Fehler " Laufzeitfehler 1004"

 

steht oben

 

Klappt irgendwie bei mir nicht. In der Basistabelle wirft er mir immer diese Fehlermeldung dann raus.
Wenn ich den Code anzeigen lasse, ist dann das erste gelb markiert.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sales Overview").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Sub Refresh()
Tabelle3.Unprotect "a"
Tabelle3.PivotTables("PivotTable1").PivotCache.Refresh
Tabelle3.Protect Password:="a", AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub

Bei mir habe ich indem Fall die Tabelle auf 3 geändert, da diese bei mir auf dritter Position ist und als testpasswort nur a hergenommen.

Das mit dem Refresh klappt auch, aber dann wenn ich den anderen code drunter schreibe, bzw. in der Tabelle mit den Pivottabellen, kommt auch die Fehlermeldung.

 

...gelbe Schrift auf weißem Hintergrund... *rolleyes.gif*

Lad die Datei hoch


Nachtrag:

...du lässt dir aber Zeit...siehe Datei.

 

Hallo,
danke für die Datei.

Anbei habe ich eine Beispielhafte Datei hochgeladen, nur mit den Daten und wie beispielsweise die zweite und Dritte Tabelle aussehen könnte.
In der Tabelle habe ich dann es beschrieben.

Muss man eine solche datei als Tabelle mit makros speichern? Wie sieht es aus wenn makros bei einigen das deaktiviert dann ist.

In deiner Datei ist es nicht automatisch das die daten in die folge datei aktualisiert werden und man kann die pivottabellen bearbeiten, also schriftart, farben etc ändern. Ich würde gerne das dies gar nichts geht.

Ich glaube ich muss ein Excel-Kurs besuchen, da solche Feinheiten doch sehr interessant wären zu wissen.