arrayformel über mehrere Tabellenblätter

ich habe folgendes Problem:
Ich habe eine Exceltabelle mit mehreren Tabellenblättern und vor Jahren mal eine Lösung geschrieben, die mir aus allen Sheets (bis auf die letzten 3) bestimmte Werte in einem array aufaddiert und die Summe in das Blatt Gesamtprovision schreibt. Das funktioniert auch gut. Nun will ich diese Berechnung aber nur für die Sheets veranlassen, die fzg1, fzg2, fzg3 usw (bis fzg15 oder so...) machen.
Klappt irgendwie nicht, bin am verzweifeln. Kann jemand helfen?
Hier der code:


I have the following problem:
I have an Excel spreadsheet with multiple worksheets and some years ago wrote a solution (except for the last 3) certain values added up to me from all sheets in a array and the sum in the leaf total commission writes. This also works well. Now I want this calculation but cause only the sheets that make fzg1, fzg2, fzg3 etc (up fzg15 or so ...).
Somehow works not'm despair. Can anyone help?
Here is the code:



Public Sub S_array()
GP = 0
BEF = 0
VNZ = 0
VNZP = 0
SP = 0
Dim a1(15, 100)

Call passwortraus
For I% = 1 To Sheets.Count - 3
Sheets(I%).Select
a1(1, I%) = Range("f41")
a1(2, I%) = Range("d49")
a1(3, I%) = Range("c4")
a1(4, I%) = Range("f49")
a1(5, I%) = Range("f51")
a1(6, I%) = Range("f54")
a1(7, I%) = Range("m19")
a1(8, I%) = Range("m21")
a1(9, I%) = Range("m23")
a1(10, I%) = Range("m25")
a1(11, I%) = Range("m27")
a1(12, I%) = Range("m29")
a1(13, I%) = Range("m37")
a1(14, I%) = Range("m45")
a1(15, I%) = Range("m48")
GP = GP + a1(6, I%)
BEF = BEF + a1(1, I%)
VNZ = VNZ + a1(2, I%)
VNZP = VNZP + a1(4, I%)
SP = SP + a1(5, I%)
Next I%
Sheets("Gesamtprovision").Select
'For i% = 1 To Sheets.Count - 3
'Range("a" & i% + 4) = a1(1, i%)
Range("b5") = BEF
Range("c5") = VNZ
Range("d5") = VNZP
Range("e5") = SP
'Range("f5") = GP
'Range("g" & i% + 4) = a1(7, i%)
'Range("h" & i% + 4) = a1(8, i%)
'Range("i" & i% + 4) = a1(9, i%)
'Range("j" & i% + 4) = a1(10, i%)
'Range("k" & i% + 4) = a1(11, i%)
'Range("l" & i% + 4) = a1(12, i%)
'Range("m" & i% + 4) = a1(13, i%)
'Range("n" & i% + 4) = a1(14, i%)
'Range("o" & i% + 4) = a1(15, i%)
'Next i%
'Cells(1, 1).Value = GP geht auch
Debug.Print "Gesamtprovision " & GP
Debug.Print "Bruttoertragsprovision Fahrzeug " & BEF
Debug.Print "Nettozubehör " & VNZ
Debug.Print "Prozente Zubehör " & VNZP
Debug.Print "Sonderprämie " & SP

Call passwortrein
End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)