Hallo,ich habe endlich genau die VBA-Prozedur, die ich zur Lösung brauche. Dank der vielen Tips konnte ich sie mit hilfe mehrerer "Fragmente" zusammenkopieren, ohne dass ich selber wirklich vba spreche. Vielen Dank für die coole Hilfe.
By the way, hier der Code, wie ich ihn verwende:
Option Explicit
Sub Mix_Customer_Data()
'Variablen erstellen
Dim Cr1 As Long, Cc1 As Integer, Cr2 As Long, Cc2 As Integer
Dim Cr3 As Long, Cc3 As Integer
Dim i As Integer, n As Integer, sumStart As Integer
Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet, wks4 As Worksheet
Dim KName As String, oldStatus
'Variablen füllen
Set wks1 = Worksheets("Kunden")
Set wks2 = Worksheets("Offerten")
Set wks3 = Worksheets("Mix")
Set wks4 = Worksheets("Template")
oldStatus = Application.StatusBar
Cr1 = 65536
Cc1 = 2 'spalte wo deine Kundennummern in der Mastertabelle stehen
Cr2 = 65536
Cc2 = 1
Cr3 = 4
Cc3 = 1
''Variablen fixieren
If wks1.Cells(Cr1, Cc1) = "" Then
Cr1 = wks1.Cells(Cr1, Cc1).End(xlUp).Row
'Anzahl Zeilen in Kundenliste zählen'
End If
If wks2.Cells(Cr2, Cc2) = "" Then
Cr2 = wks2.Cells(Cr2, Cc2).End(xlUp).Row
'Anzahl Zeilen in Offertenübersicht zählen'
End If
'Alle Daten auf wks3 löschen
wks3.Cells.ClearContents
wks3.Cells.ClearFormats
'Start der Summenbildung in wks3, beginnend in erster leeren Zeile
Copy_Mix_Structure
'Schleife für Kundendaten in Mastertabelle. Beginnt in Zeile 5
For i = 5 To Cr1
Application.StatusBar = "Datensatz " & i - 4 & " von " & Cr1 & " wird bearbeitet"
'Kundendatensatz kopieren
wks1.Cells(i, 1).Copy Destination:=wks3.Cells(Cr3, 1) 'Rang
wks1.Cells(i, 2).Copy Destination:=wks3.Cells(Cr3, 2) 'Siga Kundennummer
wks1.Cells(i, 4).Copy Destination:=wks3.Cells(Cr3, 4) 'Kundenbezeichnung SIGA
If IsEmpty(wks3.Cells(Cr3, 4)) = True Then
wks1.Cells(i, 3).Copy Destination:=wks3.Cells(Cr3, 4) 'Kundenbezeichnung SIGA
Else
End If
wks1.Cells(i, 5).Copy Destination:=wks3.Cells(Cr3, 6) 'Strasse
wks1.Cells(i, 6).Copy Destination:=wks3.Cells(Cr3, 7) 'Adress-Zusatz
wks1.Cells(i, 7).Copy Destination:=wks3.Cells(Cr3, 8) 'Postleitzahl
wks1.Cells(i, 8).Copy Destination:=wks3.Cells(Cr3, 9) 'Stadt
wks1.Cells(i, 9).Copy Destination:=wks3.Cells(Cr3, 10) 'Land
wks1.Cells(i, 10).Copy Destination:=wks3.Cells(Cr3, 11) 'Telefon
wks1.Cells(i, 11).Copy Destination:=wks3.Cells(Cr3, 12) 'Fax
wks1.Cells(i, 12).Copy Destination:=wks3.Cells(Cr3, 13) 'Bemerkung Siga
wks1.Cells(i, 13).Copy Destination:=wks3.Cells(Cr3, 14) 'Kundenbetreuer Gefco
wks1.Cells(i, 14).Copy Destination:=wks3.Cells(Cr3, 15) 'Niederlassung
wks1.Cells(i, 15).Copy Destination:=wks3.Cells(Cr3, 18) 'Bemerkung zu Kunden
wks1.Cells(i, 16).Copy Destination:=wks3.Cells(Cr3, 33) 'Umsatz 2002
wks1.Cells(i, 45).Copy Destination:=wks3.Cells(Cr3, 34) 'Umsatz Dez 2002
wks1.Cells(i, 18).Copy Destination:=wks3.Cells(Cr3, 35) 'Kunden-Cluster
wks1.Cells(i, 19).Copy Destination:=wks3.Cells(Cr3, 36) 'Ziel der Preiserhöhung
wks1.Cells(i, 22).Copy Destination:=wks3.Cells(Cr3, 37) 'verhandelte Preiserhöhung
wks1.Cells(i, 25).Copy Destination:=wks3.Cells(Cr3, 38) 'Gültigkeit der Preisänderung
wks1.Cells(i, 26).Copy Destination:=wks3.Cells(Cr3, 39) 'Absprache getroffen am
wks1.Cells(i, 27).Copy Destination:=wks3.Cells(Cr3, 40) 'Preiserhöhung im System
wks1.Cells(i, 28).Copy Destination:=wks3.Cells(Cr3, 41) 'Systempfleger
wks1.Cells(i, 29).Copy Destination:=wks3.Cells(Cr3, 42) 'Offertennummer1
wks1.Cells(i, 30).Copy Destination:=wks3.Cells(Cr3, 43) 'Offertennummer2
wks1.Cells(i, 31).Copy Destination:=wks3.Cells(Cr3, 44) 'Offertennummer3
wks1.Cells(i, 32).Copy Destination:=wks3.Cells(Cr3, 45) 'Offertennummer4
wks1.Cells(i, 33).Copy Destination:=wks3.Cells(Cr3, 46) 'Offertennummer5
wks1.Cells(i, 34).Copy Destination:=wks3.Cells(Cr3, 47) 'Offertennummer6
wks1.Cells(i, 35).Copy Destination:=wks3.Cells(Cr3, 48) 'Umsatz Jan
wks1.Cells(i, 36).Copy Destination:=wks3.Cells(Cr3, 49) 'Umsatz Feb
wks1.Cells(i, 37).Copy Destination:=wks3.Cells(Cr3, 50) 'Umsatz Mrz
wks1.Cells(i, 38).Copy Destination:=wks3.Cells(Cr3, 51) 'Umsatz Apr
wks1.Cells(i, 39).Copy Destination:=wks3.Cells(Cr3, 52) 'Umsatz Mai
wks1.Cells(i, 40).Copy Destination:=wks3.Cells(Cr3, 53) 'Umsatz Jun
wks1.Cells(i, 41).Copy Destination:=wks3.Cells(Cr3, 54) 'Umsatz Jul
wks1.Cells(i, 42).Copy Destination:=wks3.Cells(Cr3, 55) 'Umsatz Aug
wks1.Cells(i, 43).Copy Destination:=wks3.Cells(Cr3, 56) 'Umsatz Sep
wks1.Cells(i, 44).Copy Destination:=wks3.Cells(Cr3, 57) 'Umsatz Okt
wks1.Cells(i, 45).Copy Destination:=wks3.Cells(Cr3, 58) 'Umsatz Nov
wks1.Cells(i, 46).Copy Destination:=wks3.Cells(Cr3, 59) 'Umsatz Dez
'Formatieren der Kontaktzeile
wks4.Rows("6:6").Copy
wks3.Rows(Cr3).PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'Kundenname für Vergleich Schritt für Schritt runtergehen
KName = wks1.Cells(i, Cc1)
Cr3 = Cr3 + 1
For n = 2 To Cr2
'Durchsuchen aller Daten in Offertenübersicht nach Kundennummer
If wks2.Cells(n, Cc2) = KName Then
wks2.Cells(n, 1).Copy Destination:=wks3.Cells(Cr3, 2) 'SIGA-Nummer
wks2.Cells(n, 8).Copy Destination:=wks3.Cells(Cr3, 3) 'GP-Nummer
wks2.Cells(n, 21).Copy Destination:=wks3.Cells(Cr3, 4) 'Kunden-Name
wks2.Cells(n, 22).Copy Destination:=wks3.Cells(Cr3, 5) 'Namenszusatz
wks2.Cells(n, 23).Copy Destination:=wks3.Cells(Cr3, 6) 'Strasse
wks2.Cells(n, 25).Copy Destination:=wks3.Cells(Cr3, 8) 'PLZ
wks2.Cells(n, 26).Copy Destination:=wks3.Cells(Cr3, 9) 'Stadt
wks2.Cells(n, 24).Copy Destination:=wks3.Cells(Cr3, 10) 'Land
wks2.Cells(n, 2).Copy Destination:=wks3.Cells(Cr3, 15) 'Niederlassung
wks2.Cells(n, 3).Copy Destination:=wks3.Cells(Cr3, 16) 'VART
wks2.Cells(n, 4).Copy Destination:=wks3.Cells(Cr3, 17) 'ASEG
wks2.Cells(n, 5).Copy Destination:=wks3.Cells(Cr3, 19) 'Offerten-Nummer
wks2.Cells(n, 6).Copy Destination:=wks3.Cells(Cr3, 20) 'Matchcode
wks2.Cells(n, 7).Copy Destination:=wks3.Cells(Cr3, 21) 'Offerten-Bezeichnung
wks3.Cells(Cr3, 22) = Right(wks2.Cells(n, 10), 2) & "." & Left(Right(wks2.Cells(n, 10), 4), 2) & "." & Left(Right(wks2.Cells(n, 10), 6), 2) 'Gültig ab
wks3.Cells(Cr3, 23) = Right(wks2.Cells(n, 11), 2) & "." & Left(Right(wks2.Cells(n, 11), 4), 2) & "." & Left(Right(wks2.Cells(n, 11), 6), 2) 'Gültig bis
wks2.Cells(n, 12).Copy Destination:=wks3.Cells(Cr3, 24) 'Aktiv
wks2.Cells(n, 13).Copy Destination:=wks3.Cells(Cr3, 25) 'Leistung
wks2.Cells(n, 14).Copy Destination:=wks3.Cells(Cr3, 26) 'Tarif 1
wks2.Cells(n, 15).Copy Destination:=wks3.Cells(Cr3, 27) 'Marge Tarif 1
wks2.Cells(n, 16).Copy Destination:=wks3.Cells(Cr3, 28) 'Tarif 2
wks2.Cells(n, 17).Copy Destination:=wks3.Cells(Cr3, 29) 'Marge Tarif 2
wks2.Cells(n, 18).Copy Destination:=wks3.Cells(Cr3, 30) 'Zähler
wks3.Cells(Cr3, 31) = Right(wks2.Cells(n, 19), 2) & "." & Left(Right(wks2.Cells(n, 19), 4), 2) & "." & Left(Right(wks2.Cells(n, 19), 6), 2) 'letzter Zugriff
wks2.Cells(n, 20).Copy Destination:=wks3.Cells(Cr3, 32) 'LZ
'Formatierung der Offerten-Zeilen
wks4.Rows("7:7").Copy
wks3.Rows(Cr3).PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Cr3 = Cr3 + 1
End If
Next n
'letzte Zeile eines Kunden formatieren
wks4.Rows("8:8").Copy
wks3.Rows(Cr3).PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next i
Application.StatusBar = oldStatus
End Sub