Office: (Office 365) VBA bestimmte Tabellenblätter separat speichern

Hallo zusammen,

ich habe ein Dashboard erstellt, das per Datenschnitt erlaubt verschiedene Merkmale zu Filtern. Bspw. bestimmte Produkte, Varianten sowie Vertriebsmitarbeiter.
Im Hintergrund beziehen sich die Filter und Kennzahlen auf ein Blatt mit mehreren Pivot-Tabellen, das geschützt und ausgeblendet ist.
Das Dasboard selbst ist auch geschützt, um zu verhindern, dass bestimmte Datenschnitte oder Grafiken verändert werden - einzelne Datenschnitte sind jedoch vom Schutz aufgehoben.
Es hat sich auf der Wegstrecke herausgestellt, dass die Vertriebsmitarbeiter die KPIs der anderen Kollegen nicht sehen dürfen und nun jeder ein eigenes Dashboard erhalten soll.
Da es sich um rund 100 Personen handelt, benötige ein Makro, das für jeden Mitarbeiter ein eigenes Dashboard ausleitet. Ist es möglich dies über den Datenschnitt zu steuern und zusätzlich das im Hintergrund befindliche Blatt mit den Pivots jedem Extrakt zuzufügen?
Oder sollte die Dashboard-Ansicht für jede Person kopiert und vorgefiltert werden, um sie gemeinsam mit dem Pivot-Blatt ausleiten zu können?

Vielen Dank vorab für die Hilfe!

 

Ich hatte vor kurzem einen ähnlichen Fall, beidem mir Beverly geholfen hat.

Workbook über variablen Pfad öffnen (office-hilfe.com)

Der folgende Code könnte über einen Button angesprochen werden und exportiert das aktuelle Sheet und blendet den Button aus, der den Export angestoßen hat und fügt gleichzeitig noch eine Bemerkung ein.

Wenn du mehrere Blätter exportieren möchtest kannst du den Code entsprechend erweitern.

Code:

Private Sub cmdSpeichern_Click()

    'Pfad setzen
    Dim strFilename     As String
    
    'Pfad festlegen
    strFilename = "{PFAD}" & "{DATEINAME} " & Range("C2").Value & ".xls"
    
    'Blatt kopieren
    ActiveSheet.Copy
    
    'Blattschutz aufheben
    ActiveSheet.Unprotect "{PASSWORT}"
    
    'CommandButtons in neuer Mappe ausblenden
    ActiveSheet.OLEObjects("{BUTTONNAME}").Visible = False
    
    'Markierung hinzufügen
    ActiveSheet.Range("F2").Value = "Achtung diese Kopie dient nur zur Information"
    ActiveSheet.Range("F2").Font.Color = RGB(255, 0, 0)
    ActiveSheet.Range("F2").Font.Size = 16
    
    'Blattschutz wieder aktivieren
    Tabelle5.Protect "QB"
    
    'Neue Mappe speichern
    ActiveSheet.SaveAs Filename:=strFilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
      
End Sub

 

Danke für die blitzschnelle Antwort!!! Ich habe mit VBA ehrlicherweise keine Erfahrung..
Ich kann mit dem Code jedes bereits vorhandene Tabellenblatt einzeln ansteuern. Was mir fehlt ist eine Möglichkeit alle Auswahloptionen des Datenschnitts als eigene Tabellenblätter PLUS dem geschützten (am liebsten sogar versteckten) Pivot-Tabellenblatt in eigene Dateien abzuspeichern. Ist das möglich?

 

Kannst du bitte eine anonymisierte Beispieldatei hochladen? Ich glaube es wird sonst schwierig zu einem Ergebnis zu kommen.

 

Anbei eine Muster-Datei.
Normalerweise sind alle Blätter geschützt und ggfs. einzelne Elemente darin entsperrt.
In dem Tabellenblatt "Dashboard" befinden sich drei Datenschnitte auf der linken Seite - Gerne würde ich pro Datenschnitt-Feld ein Extrakt erstellen, sodass ich die Filter nicht manuell setzen, sperren und ausleiten muss. (In Realität sind es etwa 100)
Die übrigen Tabellenblätter sind erforderlich um genau diese (und zusätzliche) Datenschnitte anzusteuern und müssen immer mit extrahiert werden. Idealerweise geschützt und ausgeblendet.
Vielen Dank!

 

Erst einmal Respekt. Das ist ein schönes Dashboard. Jetzt wo ich die Tabellen sehe, kann ich mir auch vorstellen was du meinst.

Ich fürchte, da werde ich auch nicht unbedingt helfen können. Wenn es nur drei Datenschnitte oder 10 oder so wären könnte man es ja noch mit dem Makrorekorder aufnehmen, den Code schöner machen und gut, aber so benötigt es ja schon eine gewisse Logik. Ich fürchte das ist auch für mich eine Nummer zu groß, tut mir leid.

 

Wie wäre es denn, wenn es nur um einen Datenschnitt ginge oder ist auch hier die Anzahl der darin enthaltenen Elemente relevant?

Trotzdem vielen Dank dir für die schnelle Hilfe und Einschätzung!! :-)

 

Ich würde das komplett umgedreht denken. Nicht die einen kopieren, sondern die anderen rauslöschen, denn mit dem kopieren behältst du die Referenzen zur Ursprungsdatei in der Zieldatei. Wenn du stattdessen die anderen Worksheets löschst, gibt es keine externen Dateiverweise bei Worksheet übergreifenden Formeln und das Makro muss nur die Datei selbst unter anderen Namen als XLSX speichern.

Ist auch deutlich weniger Code :-)
Pan

 

Hi Pan,
danke für die Antwort!
Darüber habe ich auch nachgedacht, also das Blatt "Dashboard" in der Anzahl der Personen in derselben Datei kopieren, entsprechend vorzufiltern und entweder auszuleiten oder die überflüssigen zu entfernen. Allerdings besteht die Verbindung der Datenschnitte zu einem Blatt mit Pivot-Tabellen. In unterschiedlichen Blättern unterschiedliche Filter zu setzen, die sich auf denselben Bereich beziehen, führt dazu, dass die Pivot-Filter bei jedem neu gefilterten Blatt wieder umsetellen und somit auch alle Zahlen und Bezüge. Oder meinst du das korrigiert sich, sobald die anderen Blätter entfernt sind?

 

Hallo Pan,
vielleicht habe ich deine Antwort falsch verstanden oder sie hat mich auf eine andere Überlegung gebracht.
Möglicherweise macht es am meisten Sinn im Blatt "Data" in entsprechenden Iterationen Mitarbeiter zu entfernen und als neue Datei zu speichern.

Ich habe an anderer Stelle einen Code gefunden, der Inhalte durchsucht und Zeilen entsprechend löscht.

Sub nameloeschen()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 7) = "Max Mustermann" Then Rows(i).Delete
If Cells(i, 7) = "Michael Mustermann" Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub

Hättest du eine Idee, wie ich per Code zum einen das Ergebnis aus den entfernten Zeilen in eine separate Datei speichern kann, die nach dem übrig gebliebenen Namen benannt ist und anschließend das Löschen rückgängig macht, um den selben Vorgang für die übrigen Namen durchzuführen?
Danke!!

 

Hi rssg

Du kannst die Pivot-Filter auch mit VBA manipulieren und aktualisieren. Der Vorteil von Pivots gegenüber Formeln kommt hier gut zum tragen, da sie auch ohne die Datenquelle inhaltlich stabil bleiben. Alternativ würde das auch mit PowerQuery gehen, aber auch die müsstest du aktualisieren.

Ich würde ein Script schreiben das

• Filter modifiziert (gemäß einer Liste in der Steuerungsdatei)
• Pivot aktualisiert
• Unnötigen Worksheets löscht
• Unter neuem Namen speichert (gemäß einer Liste in der Steuerungsdatei)
• Die Datei schließt
• Die Originaldatei wieder öffnet.
• Das Script für den nächsten Anwender startet. (gemäß einer Liste in der Steuerungsdatei)

Dieses Script wäre bei mir in einer zweiten Excel-Steuerungsdatei, in der ich schnell Quell und Zielpfade modifizieren könnte und in der ich für jede Datei in einer Tabelle die Filter und Zieldateipfade hinterlegt hätte.

Das würde ich machen um das Designprinzip der Trennung von Inhalt, Parameter und Funktion zu nutzen.
Pan

 

Hi Pan,
das klingt schlüssig, allerdings habe ich null Erfahrung mit VBA - könntest du möglicherweise mit einem entsprechendem Code weiterhelfen..?

 

Das Problem besteht eher darin das du dann die kompletten Daten, samt filterlogik an mich schicken müsstest und die dann auf meinem beruflichen Rechner landen, was sie nicht dürfen .. und du magst das jetzt für einen Scherz halten, aber ich habe keinen privaten Rechner.

Sorry
Pan

 

Er tut es ja eh nicht…
VBA Tabellenblätter separat speichern
VBA Tabellenblätter separat speichern

 

Trotzdem Danke!