EXCEL VBA Code zum übetragen gefilterter Daten in anders Tabellenblatt

344 Aufrufe
Gefragt 8 Jan in Tabellenkalkulation von huskyeye_8
Hallo zusammen,

ich stehe vor folgenden Problem und könnte Hilfe gebrauchen.
Hab eine Arbeitsmappe mit 2 Datenblätter, Kunden und Tourenplanung. In Kunden sind alle Kunden aufgeführt mit Besuchsrhythmus in welcher Woche sie besucht werden müssen an welchen Datum und welcher Tag (die Berechnung habe ich mit Formeln hinterlegt.

Jetzt geht es darum alle gefilterte Kunden mit Namen (Spalte D) Straße (Spalte E) und Ort (Spalte F) die in Spalte O mit dem Wochenkürzel Mo (Montag) - Fr (Freitag) in das Datenblatt "Tourenplanung" automatisch übergeben werden.

Pro Wochentag werden ca. 8 Kunden besucht und diese Kunden/Tag sollen unter Montag - Freitag in der Tourenplanung aufgelistet werden.

Montag steht in Spalte A9 der Name des Kunden soll in Spalte B11-B19, Straße in D11-D19 und Ort in E11-E19. Dienstag steht in Spalte A23, Name des Kunden in B25-B33, Straße D25-B33 und Ort E25-E33 usw.

Es ist wichtig dass die Kunden in der IST Ansicht übernommen werden, d.h wenn der Filter gesetzt ist kann der erste Kunde für den Montag in Zeile 23 stehen.

Wäre über jeden Lösungsvorschlag happy.

Vielen Dank an alle

Siegfried

7 Antworten

0 Punkte
Beantwortet 9 Jan von m-o Profi (11,073 Punkte)
Hallo Siegfried,

leider schreibst du nicht, wie bewandert du in VBA bist.

Hier mal ein Beispiel, in dem nur gefilterte Daten eines Bereichs durchlaufen werden:
[code]Sub Beispiel()
Dim rngC As Range

   For Each rngC In Range("A1:A100").Cells.SpecialCells(xlCellTypeVisible)
           MsgBox rngC.Value
   Next

End Sub[/code]
Die jeweilige Zeile kannst du mit rngC.Row auslesen, und so die Daten aus den jeweiligen Spalten in die Tourenplanung übertragen.
Bsp:
[code]Worksheets("Tourenplanung").Range("B11") = Worksheets("Kunden").Cells(rncC.Row,4).Value[/code]
Gruß

M.O.
0 Punkte
Beantwortet 9 Jan von huskyeye_8
Hallo M.O.

vielen Dank für Deine schnelle Antwort. Leider bin ich absoluter VBA Laie.

Hatte auch versucht die Arbeitsmappe hochzuladen bzw. als Screenshot, damit man sich ein Bild machen kann, wie es aussieht und meine Vorstellung ist.

Gibt es hier eine Lösung dass ich Euch die Arbeitsmappe hoch lade, das wäre vielleicht einfacher.

Gruß

Siegfried
0 Punkte
Beantwortet 9 Jan von m-o Profi (11,073 Punkte)
Hallo Siegfried,

du kannst deine Beispieldatei auf einem externen Filehoster hochladen (z.B. [url=https://filehorst.de/]hier[/url]) und den Link dann hier posten.
Stelle aber sicher, dass keine richtigen Adressen in der Beispielmappe sind. Ein paar Fantasiedaten sollten aber schon drin stehen.

Gruß

M.O.
0 Punkte
Beantwortet 9 Jan von huskyeye_8
Hallo M.O,

vielen Dank für den Tipp :-)

anbei das Musterbeispiel:

Alle selektierten Kunden aus Spalte "K" sind Kunden die in KW 02 besucht werden sollen.
In Spalte "O" sind die Kürzel der Wochentage hinterlegt.

Mein Ziel wäre nun, alle selektierten Kunden mit Spalte "Bezeichnung, Straße, Ort" in das Datenblatt "Tourenplanung" in den jeweiligen zugeordneten Wochentag zu übernehmen, evtl. über Eingabe der Kalenderwoche.

Zum Teil sind einige Spalten "Verbunden und zentriert"


http://filehorst.de/d/cwxujyyt
http://filehorst.de/d/cvEarcFs
http://filehorst.de/d/cshoHolt


Gruß

Siegfried
0 Punkte
Beantwortet 10 Jan von m-o Profi (11,073 Punkte)
Hallo Siegfried,

hier mal die bearbeitete Datei: [url=http://filehorst.de/d/cFDkyCIx]Download[/url]

Beachte, dass das Ausführen von Makros zu gelassen sein muss ([url=http://www.excelbeispiele.de/]Anleitung[/url]).

Du brauchst nur die betreffende Woche in der Tabelle Tourenplanung einzutragen und dann den Button zu drücken. Dann wird automatisch auf die Woche gefiltert und die Daten werden übertragen. Vorher werden im Blatt eventuelle vorhandene Daten gelöscht. Die ausgeblendete Spalte in der Tabelle Tourenplanung wird ignoriert.
Teste mal, ob das Makro so funktioniert wie du dir das vorstellst.


Beachte bitte, dass wenn du die Tabellen umbenennst, das Makro entsprechend geändert werden muss.

Der Vollständigkeit halber hier das Makro (gehört in ein allgemeine Modul):

[code]Sub tourenplanung()

Dim arrTour() As Variant
Dim lngLZeile As Long
Dim lngLSpalte As Long
Dim lngWoche As Long
Dim lngFilter As Long
Dim lngZaehler As Long
Dim rngZelle As Range
Dim a As Long
Dim lngEinfZ As Long
Dim lngEinfS As Long


'Prüfen, ob in Zelle F7 etwas steht
If Worksheets("Tourenplanung").Range("F7") = "" Then
  'falls Zelle leer ist, dann Fehlermeldung ausgeben
   MsgBox "Achtung! Die Kalenderwoche ist leer! Die Verarbeitung wird abgebrochen!", 16, "Fehler - Abbruch"
   'und Makro beenden
   Exit Sub
Else
 'anderfalls wird Woche der Tourenplanung eingelesen
  lngWoche = Worksheets("Tourenplanung").Range("F7")
End If

With Worksheets("Kunden")
 'Falls in Tabelle Kunden eine Filterung aktiv, diese aufheben
  If .FilterMode Then .ShowAllData
  'Letzte Zeile im Blatt ermitteln
  lngLZeile = .Cells(Rows.Count, 1).End(xlUp).Row
  'Letzte Spalte im Blatt ermitteln
  lngLSpalte = .Cells(1, Columns.Count).End(xlToLeft).Column
  'und dann Filtern
  .Range(.Cells(1, 1), .Cells(lngLZeile, lngLSpalte)).AutoFilter Field:=11, Criteria1:="=" & lngWoche
  'und Anzahl der gefilterten Zellen feststellen
  lngFilter = .Range(.Cells(2, 1), .Cells(lngLZeile, 1)).SpecialCells(xlCellTypeVisible).Count
  'nun Array für das Einlesen der Daten redimensionieren
  ReDim arrTour(lngFilter, 3)
  'und Daten einlesen
    For Each rngZelle In .Range(.Cells(2, 15), .Cells(lngLZeile, 15)).Cells.SpecialCells(xlCellTypeVisible)
      lngZaehler = lngZaehler + 1
      arrTour(lngZaehler, 0) = rngZelle.Value    'Tag
      arrTour(lngZaehler, 1) = .Cells(rngZelle.Row, 4).Value 'Name
      arrTour(lngZaehler, 2) = .Cells(rngZelle.Row, 5).Value 'Straße
      arrTour(lngZaehler, 3) = .Cells(rngZelle.Row, 6).Value 'Ort
    Next rngZelle
 End With


With Worksheets("Tourenplanung")
 'Im Arbeitblatt Kunden eventuell vorhandene Einträge löschen
 .Range("B11:E21").ClearContents
 .Range("K11:H21").ClearContents
 .Range("B25:E35").ClearContents
 .Range("K25:H35").ClearContents
 .Range("B39:E49").ClearContents
 .Range("K39:H49").ClearContents

 'Daten in die einzelnen Tage schreiben
  For a = 1 To lngZaehler
    'Auswählen, wo Daten eingefügt werden sollen
    Select Case arrTour(a, 0)
     Case Is = "Mo"
       lngEinfZ = 11   'erste potenzielle Einfügezeile
       lngEinfS = 2    'erste Einfügespalte
     Case Is = "Di"
       lngEinfZ = 25
       lngEinfS = 2
     Case Is = "Mi"
       lngEinfZ = 39
       lngEinfS = 2
     Case Is = "Do"
       lngEinfZ = 11
       lngEinfS = 8
     Case Is = "Fr"
       lngEinfZ = 25
       lngEinfS = 8
    End Select
             
      'leere Zeile im Bereich suchen
      For lngZeile = lngEinfZ To lngEinfZ + 10
        If .Cells(lngZeile, lngEinfS) = "" Then
          .Cells(lngZeile, lngEinfS) = arrTour(a, 1)  'Name in 1. Spalte
          .Cells(lngZeile, lngEinfS + 2) = arrTour(a, 2) 'Straße in 3. Spalte (eine Spalte ausgeblendet)
          .Cells(lngZeile, lngEinfS + 3) = arrTour(a, 3) 'Ort
          Exit For
         End If
       Next lngZeile
             
   Next a

End With

End Sub[/code]
Gruß

M.O.
0 Punkte
Beantwortet 10 Jan von huskyeye_8
Hallo M.O.,

Woooowwww mege mega Stark das finde ich ist Hilfe pur. Einen Mega Dank!

Genauso hab ich es mir vorgestellt :-)

Nicht nur Deine Hilfe hat mir geholfen, hab auch ein wenig die Logik hinter VBA verstanden.
Kannst Du mir ein gutes Buch über VBA empfehlen? Wie kann ich mich bedanken und erkenntlich Zeigen für die großartige Hilfe.


Gruß

Siegfried
0 Punkte
Beantwortet 10 Jan von m-o Profi (11,073 Punkte)
Hallo Siegfried,

vielen Dank für die Rückmeldung. Das ist schon Dank genug für die Hilfe :-).

Mit VBA-Büchern ist das so eine Sache. Gehe in eine Buchhandlung und schaue dir ein paar Bücher an. Nimm das, was dir am besten gefällt.
Oder du verzichtest auf ein Buch und fängst einfach an, mal ein kleines Makro zu programmieren. Im Internet gibt es verschiedene VBA-Tutorials ([url=http://www.vba-tutorial.de/]Beispiel[/url]).  Und bei Schwierigkeiten gibt es ja immer noch das Supportnet (so habe ich mir VBA beigebracht) ;-)

Gruß

M.O.
...