Adres-DB mit Excel

[Rabe]

Hallo Rainer,

In den Blätter A-X kann ich solche Sachen wie Gruppierungen, farbliche Formatierungen usw machen, das schadet nicht?

In der Tabelle Geburten kann ich keine Spalten einfügen, wenn sich dadurch die Suchspalten verschieben? Also die Spalte R wurde z.b. zu Q.

Frage 1: Ja.
Frage 2: Jein. Es ist immer schlecht, bei Makros, die Spalten/Zeilenbezug haben, am Tabellenaufbau etwas zu ändern, denn dann muß das Makro angepasst werden.
In diesem Fall: Es wird ja nach Spalte P sortiert, wenn dann die Daten von P in Q stehen, wird trotzdem nach P sortiert und dann stimmt das Ergebnis nicht mehr.

Makro-Aktivierung:
Dieses hier ankreuzen: "Alle Makros mit Benachrichtigung deaktivieren" ist die beste Option. Dann kann bei Bedarf (aus bekannter Quelle) das Makro aktiviert werden, ansonsten sind sie nicht aktiviert.

[rainer.minnerop]

Hi Günter.

Oki, vielen Dank für den Tipp, ich stelle es um.

[atilla]

Hi.

Hab jetzt gerade noch mal die Spaltenköpfe neu kopiert, und dann die M*, die in Tabelle Filter standen rausgelöscht, danach lief alles wieder.


Also Frage für die Zukunft.

In den Blätter A-X kann ich solche Sachen wie Gruppierungen, farbliche Formatierungen usw machen, das schadet nicht?

In der Tabelle Geburten kann ich keine Spalten einfügen, wenn sich dadurch die Suchspalten verschieben? Also die Spalte R wurde z.b. zu Q.

Hallo Rainer,

Ralf hat ja schon eine Antwort darauf gegeben.
Der eingestellte Code arbeitet immer, sobald die benannten Tabellen aktiviert werden.
Auch wenn Spalten hinzukommen. Er würde dann nur keine Ergebnisse liefern. Du siehst dann nur die Überschriften, wenn die Überschriften , die in Tabelle Filter aufgeführt sind, aus dem im Code angegebenen Bereich A13:CC13 heraus fallen.

Unten stelle ich einen flexibleren Code ein. Da kannst Du dann Spalten hinzufügen oder löschen.
Die in Tabelle Filter aufgeführten Überschriften müssen aber vorhanden sein.

Die Sortierung wird abhängig gemacht von der Spalte, in der das Wort "Kind" steht. Nach dieser Spalte wird sortiert. Wenn "Kind" raus gelöscht wird, kann nicht sortiert werden und Du bekommst eine Meldung.

Fix ist, dass die Überschriften in "Geburten" in Zeile 13 stehen und in die andern Tabellen in Zeile 10 geschrieben werden.

Code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Dim lngZ As Long, lngS As Long
   Dim x
   Dim rngDaten As Range
   If Sh.Name Like "[A-Z]" Then     'wenn Blattname "A-Z"
      Application.ScreenUpdating = False
      Sh.Cells.Clear        'Blatt leeren zum neu Einlesen
      
      With Sheets("Geburten")       'In Blatt Geburten die letzte Belegte Zeile in Spalte A feststellen
         lngZ = .Cells(.Rows.Count, 1).End(xlUp).Row
         lngS = .Cells(13, .Columns.Count).End(xlToLeft).Column
         Set rngDaten = .Range(.Cells(13, 1), .Cells(lngZ, lngS))
         .Range("A4:B10").Copy Sh.Range("A2")
      End With
      
      'Spezialfilter "Oder" Filterung
      'Der Bereich A13:CC bis zur letzten belgten Zelle  wird nach Kriterienin A!:C4 in Blatt Filter gefiltert
      'und die Filterergebnisse werden in das aktivierte Blatt ab A1 eingetragen
      With Sheets("Filter")               'Mit Blatt Filter
         .Range("A2") = Sh.Name & "*"     ' in Zelle A2 den Blattnamen und  plus "*" als Platzhalter
         .Range("B3") = Sh.Name & "*"     ' in Zelle B3 den Blattnamen und  plus "*" als Platzhalter
         .Range("C4") = Sh.Name & "*"     ' in Zelle C4 den Blattnamen und  plus "*" als Platzhalter
          rngDaten.AdvancedFilter Action:=xlFilterCopy, _
              CriteriaRange:=Sheets("Filter").Range("A1:C4"), CopyToRange:=Sh.Range("A10"), Unique:=False
      End With
      
'      sortieren nach Spalte P
      With Sh
         x = Application.Match("Kind", .Rows(10), 0)
         If IsNumeric(x) Then
            lngZ = .Cells(.Rows.Count, 1).End(xlUp).Row
             .Range(.Cells(13, 1), .Cells(lngZ, lngS)).Sort Key1:=.Cells(13, x), Order1:=xlAscending, Header:=xlYes, _
                 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal
         Else
            MsgBox "Konnte nicht sortieren"
         End If
      End With
      Application.ScreenUpdating = True
   End If
  
End Sub

Nachtrag:

mir ist noch etwas aufgefallen, Du schreibst:

Hab jetzt gerade noch mal die Spaltenköpfe neu kopiert

Wenn es danach funktioniert hat, dann hast Du die Überschriften in Geburten verändert.

Das ist wichtig, die Überschriften in Filter müssen exakt so heißen wie sie in Geburten stehen.
Es ist empfehlenswert, diese aus der Tabelle Geburten zu kopieren, so wie Du es gemacht hast.

Die M* sind unerheblich, die werden bei jedem betreten der Tabelle dort neu reingeschrieben.

Hast Du eigentlich die Kommentare im Code gelesen. Das ist mehr Arbeit als den Code zu erstellen.

[Rabe]

Hi,

Du siehst dann nur die Überschriften, wenn die Überschriften , die in Tabelle Filter aufgeführt sind, aus dem im Code angegebenen Bereich A13:CC13 heraus fallen.

inzwischen gehen die Spalten anscheinend bis CE

[rainer.minnerop]

Hi.

Hast Du eigentlich die Kommentare im Code gelesen. Das ist mehr Arbeit als den Code zu erstellen.

Ja, habe gelesen, finde ich auch sehr hilfreich. :28:
Hatte auch in dem alten Code die Sortierung geändert aufgrund dieser guten Beschreibung, denn die ehemalige Reihe P ist ja jetzt R. (Kind: Familienname)

Wie das hier zu anzupassen ist, verstehe ich aber nicht? Er sortiert nach Reihe P. Nur, die ist ja nach dem Einfügen der Spalten nicht mehr zuständig. Ich hatte zuerst gedacht, ".Range(.Cells(13, 1)" ist der Bereich, aber Spalte 13 ist ja M und nicht P.

LÖSUNGSVERSUCH
Die Spalte P heißt "Kind"

Suchst du damit danach?
" x = Application.Match("Kind", .Rows(10), 0)"

Dann müsste ich auf
" x = Application.Match("Kind: Familienname", .Rows(10), 0)"
ändern?

Wenn es das ist:
Da ist ein Leerzeichen in "Kind: Familienname" stört das?

Ich teste mal mit "Kind: Familienname":

Code:

sortieren nach Spalte P
      With Sh
         x = Application.Match("Kind: Familienname", .Rows(10), 0)
         If IsNumeric(x) Then
            lngZ = .Cells(.Rows.Count, 1).End(xlUp).Row
             .Range(.Cells(13, 1), .Cells(lngZ, lngS)).Sort Key1:=.Cells(13, x), Order1:=xlAscending, Header:=xlYes, _
                 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal

Das klappt dann auch
ab Zeile 13. Die Zeilen 11+12 (in der Tabelle M sieht man das sehr gut) werden nicht mitsortiert

Muss ich dafür ".Range(.Cells(13, 1)" auf ".Range(.Cells(11, 1)" ändern?

Ich hoffe, das ist verständlich. Hört sich beim nochmaligen Lesen etwas konfus an. Jedenfalls arbeitet das Script, es sortiert auch jetzt mit der Änderung auf Kind: Familienname die richtige Spalte, nur erst ab Reihe 13

Auf jeden Fall werde ich mir das zu Herzen nehmen, und erst den kompletten Aufbau der Tabelle fertigmachen, und dann den Code einfügen. Die Mehrarbeit ist ja deutlich.

Ich habe in der ersten Tabelle "Geburten" in A3 und B3 die Zählung der Datensätze eingebaut. Dies wird dann auch von dem Script in die Tabellen A-Z eingebaut. Klappt gut. Ich hoffe, ich habe da keine verbotene Operation gemacht.

Wenn ich nerve, sag Bescheid. Ich weiß das sehr zu schätzen, dass ihr mir hier eure Weihnachtsfreizeit opfert, und möchte das nicht überstrapazieren, damit ich nicht :72: bekomme.

[Rabe]

Hi Rainer,

Ich hatte zuerst gedacht, ".Range(.Cells(13, 1)" ist der Bereich, aber Spalte 13 ist ja M und nicht P.

das ist so:

Range("A13") ist das gleiche wie Cells(13, 1) = Spalte A Zeile 13

Also Range(Spalte, Zeile) = Cells(Zeile, Spalte)

[atilla]

Hallo Rainer,

Du hast alles richtig verstanden und umgesetzt. Das Leerzeichen stört nicht.
Nur, ich habe da noch einen Fehler drin gehabt. Es wird, wie Du festgestellt hast, ab Zeile 13 sortiert.

Das ist diese Zeile:

Code:

.Range(.Cells(13, 1), .Cells(lngZ, lngS)).Sort Key1:=.Cells(13, x), Order1:=xlAscending, Header:=xlYes, _
                 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal

Da muss statt der 13 eine 10 stehen, an zwei Stellen:

Code:

'      sortieren nach Spalte P
      With Sh
         x = Application.Match("Kind: Familienname", .Rows(10), 0)
         If IsNumeric(x) Then
            lngZ = .Cells(.Rows.Count, 1).End(xlUp).Row
             .Range(.Cells(10, 1), .Cells(lngZ, lngS)).Sort Key1:=.Cells(10, x), Order1:=xlAscending, Header:=xlYes, _
                 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal
         Else
            MsgBox "Konnte nicht sortieren"
         End If
      End With

@Ralf

mit dem neuen Code wird die Anzahl der Spalten flexibel eingelesen.

[Rabe]

Hi Rainer,

Das klappt dann auch
ab Zeile 13. Die Zeilen 11+12 (in der Tabelle M sieht man das sehr gut) werden nicht mitsortiert

Muss ich dafür ".Range(.Cells(13, 1)" auf ".Range(.Cells(11, 1)" ändern?

da muß statt der 13 eine 10 hin. Denn die Überschrift zählt auch noch zum zu sortierenden Bereich dazu, wegen "Header:=xlYes".

[Rabe]

Hi Atilla,

mit dem neuen Code wird die Anzahl der Spalten flexibel eingelesen.

ja, klar, ich wollte nur drauf hinweisen.