Excel vba Array-Problem

[schauan]

Hallo Erich,

im Code findest Du die Zeile

Worksheets.Add Before:=Worksheets(1)

Das Before lässt schon vermuten, dass es auch ein After gibt.
Da die Anzahl der Blätter aber variabel ist, kannst Du dann nicht z.B. statt der 1 fest eine 13 programmieren. Hier nimmst Du dann stattdessen Sheets.Count

Das mit dem Aufführen am Ende sollte sich dann von alleine ergeben wenn Du den Teil mit dem Sortieren weg lässt.

[sharky51]

Hallo zusammen,

leider habe ich nochmals eine Frage zu dem Thema.

Für das Ausschließen von bestimmten Tabellenblättern im Inhaltsverzeichnis funktioniert der beigefügte Teil-Code perfekt.
Durch Hinzufügen oder Löschen von Blättern ist das Konstrukt aber ziemlich unflexibel, heißt man muss immer den Source-Code darauf anpassen!
Es müssen jeweils im Code diese Stellen angepasst werden.
- sheetEX = ?
'Blätter die ausgeschlossen werden
- Case "Contents", "Konfiguration", "colors56", "ToDo-Liste", "Netzwerk", "Config Daten", "Code Namen"

Wie könnte man das flexibler lösen?
Wie könnte man alle auszuschließenden Blätter, die namentlich irgendwo in einem variablen Tabellenbereich aufgeführt sind und somit auch die Anzahl der Einträge bekannt sind,
in diesen Case-Konstrukt übernehmen?
Oder gäbe es noch eine intelligentere Lösung?

Für Eure Hilfe wäre ich dankbar!

---snip------

Code:

'Create Array list with sheet names (excluding several sheets)
'sheetEX ist die Anzahl der auszuklammernden Sheets
sheetEX = 7
ReDim myArray(1 To Worksheets.Count - sheetEX)

For Each sht In ThisWorkbook.Worksheets
  Select Case sht.Name
     'Blätter die ausgeschlossen werden
     Case "Contents", "Konfiguration", "colors56", "ToDo-Liste", "Netzwerk", "Config Daten", "Code Namen"
     Case Else
        myArray(x + 1) = sht.Name
        x = x + 1
  End Select
Next sht

'Alphabetize Sheet Names in Array List
For x = LBound(myArray) To UBound(myArray)
  For Y = x To UBound(myArray)
     If UCase(myArray(Y)) < UCase(myArray(x)) Then
        shtName1 = myArray(x)
        shtName2 = myArray(Y)
        myArray(x) = shtName2
        myArray(Y) = shtName1
     End If
  Next Y
Next x

'Create Table of Contents
For x = LBound(myArray) To UBound(myArray)
  Set sht = Worksheets(myArray(x))
  'sht.Activate
  With Worksheets("Contents")
     .Hyperlinks.Add .Cells(x + 2, 3), "", _
     SubAddress:="'" & sht.Name & "'!A1", _
     TextToDisplay:=sht.Name
     .Cells(x + 2, 2).Value = x
  End With
Next x

[aloys78]

Hallo,

nachfolgend eine Möglichkeit.

1. Ausnahmen in einem Tabellenblatt definieren und im Namensmanager mit einem Namen versehen, zB "Ausnahmen"

2. den Select-Rahmen durch folgenden Code ersetzen.

Code:

Dim sht As Worksheet

Dim erg As Variant

For Each sht In ThisWorkbook.Worksheets
   erg = Application.Match(sht.Name, Range("Ausnahmen"), 0)
   If Not IsNumeric(erg) Then      ' aktuelles Sheet nicht in Ausnahmeliste
       myArray(x + 1) = sht.Name
       
       
   End If
Next sht
Gruß
Aloys

[schauan]

hallöchen,

hier mal ein kurzes Beispiel. Die Blattnamen holst Du aus einem Bereich, hier das aktive Blatt und A1:A2. Den Bereich kannst Du auch variabel gestalten, indem Du die letzte Zeile per Code ermittelst.

Code:

Option Explicit

Sub test()
Dim arrNamen, sht As Worksheet, b
arrNamen = WorksheetFunction.Transpose(Range("A1:A2").Value)
For Each sht In ThisWorkbook.Worksheets
  b = Filter(arrNamen, sht.Name, True, vbTextCompare)
  If UBound(b) > -1 Then If sht.Name = b(0) Then MsgBox sht.Name
Next
End Sub

[Kuwer]

Hallo Erich,

Es müssen jeweils im Code diese Stellen angepasst werden.
- sheetEX = ?

wofür wird sheetEX überhaupt benötigt?

Gruß Uwe

[sharky51]

Hallo Uwe,

das ist eine Variable wo ich einfach die Anzahl der auszublenden Blätter angebe.
Das ist ja auch einer der Gründe warum ich das frei von manuellen Codeänderungen gestalten möchte.

Dein Beispiel ist gut, aber wie baue ich das in meinen Code mit den "Case" ein?
Das was ich gezeigt habe ist ja nur ein Teil vom Code für ein Inhaltsverzeichnis erstellen.

[schauan]

Hallöchen,

das sheetEx ist für die Dimensionierung des temp. Arrays für die Sortierung weiter unten, mal unabhängig, ob man das so machen muss
Erich, Du sowieso auf einem Blatt die auszuschließenden Blätter beschreist kannst Du auch die einzuschließenden dort hinlegen und per Makro sortieren

Oder hier mal ein Formelansatz. In Spalte A wird per Formel eine Blattübersicht erstellt, in Spalte B würdest Du die Blätter manuell eintragen, in Spalte C wird aussortiert und in Spalte E dann sortiert.

Arbeitsblatt mit dem Namen 'Willi' A B C D 1 Willi Willi Tabelle4 Tabelle3 2 Tabelle4 Bald Tabelle3 Tabelle4 3 Tabelle3 4 Bald
Name Bezug Blatt =ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())	Zelle Formel A1 =WENN(ZEILE(A1)>ANZAHL2(Blatt);"";HYPERLINK("#'"&INDEX(Blatt;ZEILE(A1))&"'!A1"; TEIL(INDEX(Blatt;ZEILE(A1));FINDEN("]";INDEX(Blatt;ZEILE(A1)))+1;31))) C1 {=WENNFEHLER(INDEX(A:A;KKLEINSTE(WENN(A$1:A$99<>"";WENN(ZÄHLENWENN(B$1:B$99;A$1:A$99)=0;ZEILE(Y$1:Y$99)));ZEILE(Y1)));"")} D1 {=INDEX(C:C;VERGLEICH(KGRÖSSTE(ZÄHLENWENN(C$1:C$99;">="&C$1:C$99)+99*ISTZAHL(C$1:C$99);ZEILEN($1:1));ZÄHLENWENN(C$1:C$99;">="&C$1:C$99)+99*ISTZAHL(C$1:C$99);0))&""}
Achtung, Matrixformel enthalten! Die geschweiften Klammern{} werden nicht eingegeben. Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

[sharky51]

Aloys,

verstehe ich da etwas falsch - in Deinem Beispiel müsste ich ja auch händisch bei jeder Änderung alles nachtragen - oder?

---

Hallo André,

danke auch für Deinen Vorschlag, es soll aber alles per vba abgewickelt werden.
An anderer Stelle werde ich sicher darauf zurückkommen.