[VBA] Inhaltsverzeichnis von Arbeitsblättern....

Bild

Betrifft: [VBA] Inhaltsverzeichnis von Arbeitsblättern....
von: MaWe
Geschrieben am: 13.08.2015 07:26:27

Hallo,
es wäre wirklich top wenn mir jemand zeigen könnte wie man per VBA ein Inhaltsverzeichnis der ganzen Arbeitsblätter in einer Mappe erstellen kann!
Ich möchte also die Namen der ganzen Arbeitsblätter aufgelistet bekommen.
Toll wäre es, wenn man explizit angeben könnte wohin das IHV gesetzt wird und in welcher Zeile und Spalte es beginnen soll (aber kein muss)
Eine Option auf eine direkte Velinkung wäre auch toll!
Das ganze hätte ich zusätzlich noch gerne für den Titel der Pivot Charts.
Wenn mir diesbezüglich einer helfen könnte ware das schön :)

Bild

Betrifft: AW: [VBA] Inhaltsverzeichnis
von: Bernd
Geschrieben am: 13.08.2015 07:31:19
Guten Morgen,
nicht von mir, aber probier mal:
Option Explicit ' IMMER zu empfehlen

Sub sbName()
   Dim strN As String, X As Long, Y As Long, i As Long
'  Application.ScreenUpdating = False     ' evtl. NACH dem Test aktivieren
   With Sheets("01. Übersichtsliste")
      .Unprotect
      For X = 1 To Worksheets.Count - 1
         For Y = X + 1 To Worksheets.Count
            If Worksheets(Y).Name < Worksheets(X).Name Then
               Worksheets(Y).Move Before:=Worksheets(X)
            End If
         Next Y
      Next X
      .Activate
   
      Range("A:B").ClearContents
      Application.PrintCommunication = False
      For i = 1 To Worksheets.Count
         strN = Worksheets(i).Name
         .Cells(i + 2, 2) = strN
         .Hyperlinks.Add Anchor:=Cells(i + 2, 2), Address:="", _
            SubAddress:="'" & strN & "'!A1", TextToDisplay:=strN
         If .Name <> strN Then
            .Cells(i + 2, 1) = i - 1
            Worksheets(i).Cells(2, 2).Value = i - 1
            Worksheets(i).PageSetup.RightFooter = CStr(i - 1)
         End If
      Next i
      .Columns("A:A").EntireColumn.AutoFit
      Application.PrintCommunication = True
      Application.GoTo Reference:="R3C1"
      .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   End With
   Application.ScreenUpdating = True
End Sub


Rückmeldung wäre nett !!! 


>>> mfg Bernd <<<

Sicher ist, dass nichts sicher ist. Selbst das nicht.


Bild

Betrifft: AW:Nachtrag!
von: Bernd
Geschrieben am: 13.08.2015 07:39:25
Hier ohne zu schützen und fortlaufend nummerieren:

Sub sbName()
   Dim strN As String, X As Long, Y As Long, i As Long
'  Application.ScreenUpdating = False     ' evtl. NACH dem Test aktivieren
   With Sheets("Tabelle1")
      For X = 1 To Worksheets.Count - 1
         For Y = X + 1 To Worksheets.Count
            If Worksheets(Y).Name < Worksheets(X).Name Then
               Worksheets(Y).Move Before:=Worksheets(X)
            End If
         Next Y
      Next X
      .Activate
   
      Range("A:B").ClearContents
      Application.PrintCommunication = False
      For i = 1 To Worksheets.Count
         strN = Worksheets(i).Name
         .Cells(i + 2, 2) = strN
         .Hyperlinks.Add Anchor:=Cells(i + 2, 2), Address:="", _
            SubAddress:="'" & strN & "'!A1", TextToDisplay:=strN
      Next i
      .Columns("A:A").EntireColumn.AutoFit
      Application.PrintCommunication = True
      Application.GoTo Reference:="R3C1"
   End With
   Application.ScreenUpdating = True
End Sub

mfg Bernd

Bild

Betrifft: AW: [VBA] Inhaltsverzeichnis von Arbeitsblättern....
von: MaWe
Geschrieben am: 15.08.2015 18:04:15
Hi Uwe funktioniert :) danke schonmal.
zwei Dinge noch.
Am Anfang des Dokumentes muss man explizit angeben wie das Tabellenblatt heißen muss, auf dem es geschrieben wird. Kann man dies irgendwie ändern?
Er fängt an das IHV in Zelle/Zeile B3 zu schreiben. Wie kann ich das ändern? Kenne mich leider nicht so gut in vba aus, und finde kein Anhaltspunkt wo man erkennen kann das er es genau dahin hinschreibt.
Und letzter Punkt. Das Skript ist ja "nur" für die Tabellenblätter. Wie bekomme ich die Diagramm Titel (Pivot Charts) in das IHV? Diese müssen auch nicht verlinkt sein, reicht wenn der Text 1:1 übernommen wird und aufgelistet wird.
und wo wir grade dabei sind. Was mir sehr wichtig ist.
Gibt es nicht eine Möglichkeit das er mir alles auflistet SO WIE ES IST?
Bei deinem Skript sortiert er alles alphabetisch, dann muss ich es nachträglich alles wieder "richtig" ordnen!
Ich will das er mir das IHV genau in der Reihenfolge ausgibt wie sie in der Mappe zu finden sind!
Ich weiß ne Menge Holz auf einmal, aber vielleicht kann mir da wer helfen :)
VIELEN DANK schonmal :)

Bild

Betrifft: AW: IHV
von: Bernd
Geschrieben am: 16.08.2015 06:35:34
Hallo,
Zuerst eine Frage? Wer ist Uwe?
Frage 1: So wie Dein Tabellenblatt betitelt ist, musst Du auch den selben Namen im Makro verwenden
Frage2: Ändere einfach den Zellbezug im Makro um nicht in B3 zu Beginnen obwohl Du in Deiner Anfrage geschrieben hast, es sei Dir egal wo es beginnt --> (aber kein muss)
Frage 3: Die Sortierung habe ich aus dem Code gelöscht, vergleiche einfach beide, dann weißt Du wo
hier mal mein neuer Code mit ein paar kleinen Anleitungen:

Sub IHV()
   Dim strN As String, X As Long, Y As Long, i As Long
   With Sheets("Tabelle1") ' hier Deinen Blattnamen eingeben
      Range("A:B").ClearContents ' dieser Bereich wird vor Erstellung eines neuen IHV gelöscht
      Application.PrintCommunication = False
      For i = 2 To Worksheets.Count ' Zahl 1 wäre mit dem Tabellenblatt1, 2 ohne Tabellenblatt1  _
aufzulisten usw.
         strN = Worksheets(i).Name
         .Cells(i + 1, 2) = strN ' 1,1 ist Beginn in Zelle A1, 1,2 ist Zelle B3 usw.
         .Hyperlinks.Add Anchor:=Cells(i - 1, 2), Address:="", _
            SubAddress:="'" & strN & "'!B1", TextToDisplay:=strN
      Next i
      .Columns("A:A").EntireColumn.AutoFit
      Application.PrintCommunication = True
      Application.GoTo Reference:="R3C1"
   End With
   Application.ScreenUpdating = True
End Sub
Abschließend möchte ich sagen, ich bin kein VBA Profi, sondern probiere selber nur an den Codes anderer herum. Selbiges bezieht sich auch auf die Pivot Charts, da kann ich leider nicht weiter helfen.
Da wird sich sicher jemand anderer finden.
Hoffe wir sind Deinem Ziel wieder etwas näher gekommen?
Dann noch schönen Sonntag,
mfg Bernd

Bild

Betrifft: AW: IHV Nachtrag
von: Bernd
Geschrieben am: 16.08.2015 06:37:43
hier noch ein nützlicher Link bzgl. der Zellbezüge:
http://ms-excel.eu/faqs/excel-vba-texte/vba-zellen-und-bereiche.html
mfg Bernd

Bild

Betrifft: AW: [VBA] Inhaltsverzeichnis von Arbeitsblättern....
von: MaWe
Geschrieben am: 16.08.2015 14:53:02
Hi Bernd, (sorry nochmal, keine Ahnung warum ich Uswe geschrieben habe :D)
ja jetzt stimmt schon einmal die Reihenfolge :)
Aber wo er starten soll habe ich immer noch nicht verstanden.
bei 1,1 startet er in A1 bei 1,2 in B1
wie schaffe ich es aber das er z.B. in B4 startet (bevor du meckerst. mit 4,2 geht es nicht, probiere es aus)

Bild

Betrifft: AW: ab B4
von: Bernd
Geschrieben am: 16.08.2015 18:00:43
Hallöchen,
für B4 so:

Sub IHV()
   Dim strN As String, X As Long, Y As Long, i As Long
   With Sheets("Tabelle1") ' hier Deinen Blattnamen eingeben
      Range("A:B").ClearContents ' dieser Bereich wird vor Erstellung eines neuen IHV gelöscht
      Application.PrintCommunication = False
      For i = 2 To Worksheets.Count ' Zahl 1 wäre mit dem Tabellenblatt1, 2 ohne Tabellenblatt1  _
_
aufzulisten usw.
         strN = Worksheets(i).Name
         .Cells(i + 2, 2) = strN ' 1,1 ist Beginn in Zelle A1, 1,2 ist Zelle B3 usw.
         .Hyperlinks.Add Anchor:=Cells(i + 2, 2), Address:="", _
            SubAddress:="'" & strN & "'!B1", TextToDisplay:=strN
      Next i
      .Columns("A:A").EntireColumn.AutoFit
      Application.PrintCommunication = True
      Application.GoTo Reference:="R3C1"
   End With
   Application.ScreenUpdating = True
End Sub
Am besten einfach selber probieren bis es klappt. Bei so einem kleinen Code ist es ohnehin sehr übersichtlich.
Stimmt es nun so?
mfg Bernd

Bild

Betrifft: AW: [VBA] Inhaltsverzeichnis von Arbeitsblättern....
von: MaWe
Geschrieben am: 16.08.2015 18:43:00
Hi Bernd,
ja jetzt funktioniert es danke.
Ich bin leider ein absoluter noob und habe davon gar keine Ahnung, das mit dem Rumprobieren lasse ich lieber sein, bevor am Ende mein Dokument hinüber ist, daran habe ich schon eine halbe Ewigkeit gebastelt!
Und von dem Code versteh ich gar nichts....
für mich stellen sich dort folgende Fragen.
Für was genau brauche ich das i?
Wenn das nicht wäre könnte man ja "normal" zählen und müßte nicht als umrechnen (+/- 2)
für was ist dieser Absatz?

.Columns("A:A").EntireColumn.AutoFit
      Application.PrintCommunication = True
      Application.GoTo Reference:="R3C1"
Frage mich halt was er in A sucht oder macht? Da soll ja gar nichts gemacht werden?!?
und nochmal wegen den Diagrammtiteln. Ich habe diese nun alle dynmaisch hinterlegt.
Die Information dafür befindet sich auf jedem Arbeitsblatt in B2
Wie kann ich daraus nun ein IHV erstellen? Das sollte doch nun deutlich einfacher sein.

Bild

Betrifft: AW: IHV
von: Bernd
Geschrieben am: 17.08.2015 05:15:07
Hi nochmals,
man kann sich ja auch ne Sicherungskopie erstellen zum herum probieren !!!
Wenn Du nur einen Teil des Codes benötigst, dann lösche doch die restlichen Bereiche bzw. schreibe diese nach Deinen Wünschen um und teste dann, ob alles so wie gewünscht funktioniert.
Ich kann mich nur zum wiederholten Male äußern, dass ich KEIN VBA PROFI bin, ergo, wenn Du in VBA Coaching möchtest, bitte andere fragen oder so wie ich immer mache, Googeln!
mfg Bernd

Bild

Betrifft: AW: [VBA] Inhaltsverzeichnis von Arbeitsblättern....
von: MaWe
Geschrieben am: 17.08.2015 09:06:19
@Bernd danke für deine Hilfe
@All
kann mir jemand behilflich sein wie man aus den Diagrammtiteln ein IHV basteln kann? Wie gesagt sind diese mittlerweile dynamisch und bedinfen sich in jedem Arbeitsblatt an der selben Stell (B2)

Bild

Betrifft: AW: [VBA] Inhaltsverzeichnis von Arbeitsblättern....
von: AlexG
Geschrieben am: 18.08.2015 09:44:46
Hallo MaWE,
So?
eine kleine Beispieldatei ist immer von vorteil, damit man auch testen kann.
Denn so was baut keiner nach.
Modul Modul1

Option Explicit 
Sub IHV() 
Dim ws As Worksheet 
Dim rng As Range 
Dim pt As PivotTable 
Dim i, iRow, iRowPT As Integer 
With Sheets(1).Range("C5") 'Startzelle evtl anpassen 
For i = 2 To Worksheets.Count 
    .Offset(iRow).Hyperlinks.Add Anchor:=.Offset(iRow), _
    Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", _
    TextToDisplay:=Sheets(i).Name 
    iRow = iRow + 1 
    For Each pt In Sheets(i).PivotTables 
        .Offset(iRow - 1 + iRowPT, 1) = pt.Name 
        iRowPT = iRowPT + 1 
    Next pt 
    If iRowPT > 0 Then 
        iRow = iRow + iRowPT - 1 
        iRowPT = 0 
    End If 
Next i 
End With 
End Sub 

Gruß
Alex

 Bild

Beiträge aus den Excel-Beispielen zum Thema "[VBA] Inhaltsverzeichnis von Arbeitsblättern...."