(05.01.2018, 18:51)Alooha schrieb: wie kann ich per VBA alle Blätter eins WB's gruppieren, dann verschiedene Subs laufen lassen, und dann die Gruppierung wieder aufheben?
Dim oWs As Worksheet
Set oWs = ActiveSheet
ThisWorkbook.Worksheets.Select
vielen Dank für eure Antworten!
Klaus-Dieter, du hast Recht, und weil du Recht hast, habe ich ein Beispiel zusammengestellt. Es handelt sich um Daten Angestellte betreffend.
Die Sheets auswählen ist in Wirklichkeit nur ein kleiner Teil der Aufgabe. Zuerst glaubte ich, dem Rest gewachsen zu sein, aber es ist komplizierter als ich glaubte und es übersteigt meine VBA Kapazitäten.
Um von vorne zu beginnen: das Ganze ist die Schuld der verflixten Funktion INDIRECT() die ja nun mal nicht arbeiten will wenn das File auf das die Formeln sich beziehen nicht geöffnet ist.
Ich habe 2 Files präpariert, die sich eng an die echten Files anlehnen. Da das ganze Projekt (dies soll lediglich eine Ergänzung sein) in frz. Sprache ist, sind meine Beispielfiles es auch, aber das dürfte kein Problem sein.
Meine Files:
* Base : enthält Personnel und SERVICE 1 bis 4 (in Wirklichkeit 10)
Da es in Personnel von D3:AI489 tonnenweise Formeln gibt die allerhand Berechnungen machen, hole ich mir die Daten aus den SERVICES in Personnel, sodass ich nicht 10 mal diese vielen Formeln brauche. Ich hole sie mir, indem ich in Personnel : A1 den Namen des SERVICE schreibe, im Beispiel SERVICE 3.
* SERVICE 3 2017
Jeder Service hat sein File mit einem Blatt pro Angestelltem und ein Modellfile "Modèle" (dient dazu, ein neues, identisches Blatt zu produzieren).
Wegen der INDIRECT Funktion habe ich die Daten nicht zur Verfügung a) wenn "Base" nicht geöffnet ist und b) wenn bei geöffnetem "Base" in "Personnel" A1 nicht "SERVICE 3" steht.
Daher habe ich geplant per VBA (in "Base") folgendes zu tun:
1. aus "Base" heraus "SERVICE 3" öffnen
2. dann habe ich 2 Alternativen entdeckt:
I) Alternative I
1. alle Blätter ausser "Modèle" auswählen und "kollektiv" (bin aber nicht sicher ob das möglich ist; mir scheint dass, wenn ich die Makro abarbeite, ein Blatt nach dem anderen drankommt) in jede Zelle von B6:H8 die betreffende Formel schreiben, Zelle pro Zelle
2. in "Base" wechseln und in A1 des Blattes "Personnel" "SERVICE 3" schreiben
womit die Formeln in SERVICE 3 ihre Werte produzieren
3. Zurück in SERVICE 3 wechseln, wo noch alle Blätter ausser "Modèle" ausgewählt sind
B6:H8 kopieren und als Werte in das gleiche Areal zurückpasten
4. die Auswahl aufheben (z.B. durch Auswählen von "Modèle") und SERVICE 3 abspeichern.
Wenn ich es dann wieder öffne (jeden Monat kommen Daten dazu; die habe ich in meinem Beispiel weggelassen weil sie irrelevant sind) dann habe ich Werte anstatt der #N/A.
II) Alternative II (schneller, aber "Modèle" darf auf keinen Fall mit den anderen Blättern ausgewählt werden, damit die Formeln dieses Blattes erhalten bleiben)
1. "Modèle" auswählen
2. die Formeln die sich in B6:H8 befinden kopieren
3. alle anderen Blätter auswählen
4. die kopierten Formeln nach B6:H8 aller Blätter pasten
5. die Auswahl aufheben (z.B. durch Auswählen von "Modèle") und SERVICE 3 abspeichern.
Ich hoffe, mich verständlich ausgedrückt zu haben.
06.01.2018, 23:38 (Dieser Beitrag wurde zuletzt bearbeitet: 06.01.2018, 23:38 von Alooha.)
...was übrigens sehr strange ist: im VBA meines Originalfile's habe ich eine Funktions-Sub mit Namen Sub Ostern(), die das Datum von Ostern berechnet und wenn ich meinen Code step by step (F8) ausführe, dann lande ich immer in dieser Sub, gleich nach dem Oeffnen von SERVICE3. Die wird dann durchlaufen und dann geht es weiter. Wie ist sowas möglich. Im Code steht kein Wort von Ostern!
Mir ist eben aufgefallen, dass die Endung von "Base" falsch ist im Code:sie ist "xlsm" und nicht "xlsx". Sorry
ich habe mir eine funktionierende Lösung zusammengebastelt aus Code aus dem Internet und aufgezeichnetem:
Code:
Sub Remplacer_formules()
'die Formeln ersetzen
Dim wSht As Worksheet
Sheets("Personnel").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "SERVICE 3"
Workbooks.Open Filename:= _
"C:\Users\...\Desktop\Exemple 5.1.18\SERVICE 3\SERVICE 3 2017.xlsx"
Sheets("Modèle").Visible = True
Sheets("Modèle").Select
Range("B6:H8").Select
Selection.Copy
For Each wSht In Worksheets
If wSht.Name <> "Modèle" Then
wSht.Select
Range("B6:H8").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Next wSht
For Each wSht In Worksheets
If wSht.Name <> "Modèle" Then
wSht.Select
Range("B6:H8").Select
Selection.Copy
Range("B6:H8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next wSht
Sheets("Modèle").Visible = False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
Was haltet ihr davon? Er ist übrigens ultraschnell, was ja kein Nachteil ist!
Wo ich jetzt eine Hilfe bräuchte, das wäre beim nächsten Schritt: Excel dazu bringen,
alle 10 Dateien eine nach der anderen zu öffnen, die Formeln zu pasten, sie durch ihre
Werte zu ersetzen, die Datei abzuspeichern und die nächste zu öffnen. Das übersteigt klar meine VBA-Kapazitäten!
Dazu müsstet ihr natürlich den Pfad der Dateien kennen:
