Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

mehrere Spalte kop. in vorh. Formatierung einfügen

mehrere Spalte kop. in vorh. Formatierung einfügen
07.08.2017 14:14:29
Daniel
Guten Tag,
ich habe folgende Aufgabenstellung.
Ich habe ein Excel Export aus einem ERP System, dieser aktualisiert sich wöchentlich.
Der Anwender formatiert die Tabelle jede Woche neu, in sein entsprechende Standard Format welches er benötigt.
Somit habe ich nun also eine Excel Datei als "Vorlage" mit der Standard Formatierung.
Nun soll hierbei durch VBA der manuelle copy&paste Vorgang beschleunigt werden.
In der Excel Export Datei gibt es auch Spalten die leer sind, diese sollen nicht mit kopiert werden. Der Aufbau der Excel Export Datei aus dem ERP ist jedes mal gleich, nur die Werte ändern sind.
Des weiteren gibt es 12 Spalten mit den aktuellem Datumswert als Tabellenüberschrift.
Bsp: "PBD-2017-07-12" dies soll nun als korrektes Datum ausgegeben werden "TT.MM.JJJJ" in der Zeile darunter, soll dann jeweils der richtige Wochentag ermittelt und ausgegeben werden, z.B. "Mittwoch".
Mit folgendem Code habe ich bisher eine recht praktikable Lösung gefunden, allerdings wird das Format, die Tabellenüberschrift mit kopiert und nicht in der Zielzeile A4 begonnen den Wert einzufügen.
Public Sub Kopieren()
Dim WkSh_Quelle As Worksheet
Dim WkSh_Ziel As Worksheet
Dim rZelle As Range
Dim aUeberschr As Variant
Dim iIndx As Integer
Dim iSpalte As Integer
aUeberschr = Array("Arbeitsplatz", "Material", "Bezeichnung", "Abladestelle")
Application.ScreenUpdating = False
Set WkSh_Quelle = Worksheets("Tabelle2") ' das Quell-Tabellenblatt
Set WkSh_Ziel = Worksheets("Import_Sheet") ' das Ziel-Tabellenblatt
With WkSh_Quelle.Rows(1)
For iIndx = 0 To UBound(aUeberschr)
Set rZelle = .Find(aUeberschr(iIndx), LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
iSpalte = iSpalte + 1
WkSh_Quelle.Columns(rZelle.Column).Copy Destination:=WkSh_Ziel.Columns(iSpalte)
End If
Next iIndx
End With
Application.ScreenUpdating = True
End Sub


Geplant ist das folgendermaßen umzusetzen. Es gibt einen Excel Export aus einem ERP System dieser soll in eine neue Excel-Datei (z.B. Zwischenablage) eingelesen werden, in der sich auf die Vorlage befindet. Im Anschluss soll die Vorlage befüllt und eine neue Datei erstellt werden.
Über Unterstützung freue ich mich.

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
07.08.2017 14:43:52
ChrisL
Hi Daniel
Vielleicht so... (ansonsten bitte um Beispieldatei)
Public Sub Kopieren()
Dim WkSh_Quelle As Worksheet
Dim WkSh_Ziel As Worksheet
Dim rZelle As Range
Dim aUeberschr As Variant
Dim iIndx As Integer
Dim iSpalte As Integer
Dim lZeile As Long
aUeberschr = Array("Arbeitsplatz", "Material", "Bezeichnung", "Abladestelle")
Application.ScreenUpdating = False
Set WkSh_Quelle = Worksheets("Tabelle2") ' das Quell-Tabellenblatt
Set WkSh_Ziel = Worksheets("Import_Sheet") ' das Ziel-Tabellenblatt
With WkSh_Quelle
For iIndx = 0 To UBound(aUeberschr)
Set rZelle = .Rows(1).Find(aUeberschr(iIndx), LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
iSpalte = iSpalte + 1
lZeile = .Cells(Rows.Count, rZelle.Column).End(xlUp).Row
WkSh_Ziel.Range(WkSh_Ziel.Cells(4, iSpalte), WkSh_Ziel.Cells(lZeile + 2, iSpalte)) = _
.Range(.Cells(2, rZelle.Column), .Cells(lZeile, rZelle.Column)).Value
End If
Next iIndx
End With
Application.ScreenUpdating = True
End Sub

cu
Chris
Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
07.08.2017 15:04:33
Daniel
Vielen Dank, das hat schon mal funktioniert wie es soll.
Besteht die Möglichkeit in der Zieltabelle die Formatierung nach unten soweit dynamisch zu generieren, soviel Inhalt eingefügt wird?
Es kann durchaus vorkommen das einmal mehr Inhalt vorhanden ist und einmal weniger.
Im Anhang die Test-Datei: (ist noch komplett ohne VBA Code)
https://www.herber.de/bbs/user/115309.xlsx
Vielen Dank!
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
07.08.2017 15:40:57
ChrisL
Hi Daniel
So wird Formatierung von Zeile 4 kopiert...
Public Sub Kopieren()
Dim WkSh_Quelle As Worksheet
Dim WkSh_Ziel As Worksheet
Dim rZelle As Range
Dim aUeberschr As Variant
Dim iIndx As Integer
Dim iSpalte As Integer
Dim lZeile As Long
aUeberschr = Array("Arbeitsplatz", "Material", "Bezeichnung", "Abladestelle")
Application.ScreenUpdating = False
Set WkSh_Quelle = Worksheets("Tabelle1") ' das Quell-Tabellenblatt
Set WkSh_Ziel = Worksheets("Import_Sheet") ' das Ziel-Tabellenblatt
With WkSh_Quelle
For iIndx = 0 To UBound(aUeberschr)
Set rZelle = .Rows(1).Find(aUeberschr(iIndx), LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
iSpalte = iSpalte + 1
lZeile = .Cells(Rows.Count, rZelle.Column).End(xlUp).Row
WkSh_Ziel.Range(WkSh_Ziel.Cells(4, iSpalte), WkSh_Ziel.Cells(lZeile + 2, iSpalte)) = _
.Range(.Cells(2, rZelle.Column), .Cells(lZeile, rZelle.Column)).Value
End If
Next iIndx
End With
WkSh_Ziel.Rows("4:4").Copy
WkSh_Ziel.Rows("5:" & lZeile + 2).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

cu
Chris
Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
07.08.2017 15:57:22
Daniel
Hey Chris,
genial! So ist das wirklich sehr komfortabel.
Wie bekomme ich denn nun noch folgendes gelöst:
Ab Spalte H beginnen die Datumsfelder, hier sollen die ersten 12 Spalten kopiert und ab Spalte G im Ziel Arbeitsblatt eingefügt werden.
Das Datum sollte dann selber auch noch angepasst werden.
Bsp: "PBD-2017-07-12" dies soll nun als korrektes Datum ausgegeben werden "TT.MM.JJJJ" in der Zeile "3", soll dann jeweils der richtige Wochentag ermittelt und ausgegeben werden, z.B. "Mittwoch".
Für deine Hilfe danke ich dir im Voraus.
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 08:54:38
ChrisL
Hi Daniel
Verstehe dich leider nur ansatzweise und die Aufgabe wird mir auch langsam ein wenig (zu) umfangreich. Salamitaktik zieht bei mir nicht ;) Du kannst die Frage aber als "offen" kennzeichnen.
Dennoch folgender Hinweis zum Datum. Wenn du ein "richtiges" Datum verwendest (Datentyp = Datum und nicht Text-String), dann kannst du dieses beliebig formatieren u.a.
TTTT = Wochentag
oder... "PBD"-JJJJ-MM-TT
cu
Chris
Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 09:08:58
Daniel
Hey Chris,
ich versuche es mal anders zu beschreiben.
In meinem Export Datenblatt habe ich von Spalte U - HJ Datumsfelder (in der Überschrift) und darunter die dazugehörigen Werte.
Ich möchte nun die Überschrift aus dem Export Datenblatt in die erste Zeile in meine Ziel-Tabelle kopieren, hier beginnen die Spalten von G-R.
Darauf folgend sollen die Werte der jeweiligen Spalte ab Zeile 4 in der Ziel-Tabelle geschrieben werden.
Ich versuche das ganze aktuell über den Namens-Manager aufzulösen, damit ich die Spalte jeweils ansprechen kann. Da sich das Datum ja jedes mal ändert kann ich den VBA Code mit den Überschriften nicht 1:1 verwenden.
Ist der Ansatz über den Namensmanager eine praktikable Lösung ?
Ich brauche jetzt nicht zwingend ein Code-Beispiel, bin mir aber unsicher wie ich da am besten vorgehen kann.
Danke dir für deine Unterstützung.
Daniel
Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 10:47:57
ChrisL
Hi Daniel
Ich sehe leider kein Blatt "Export" und auf Tabelle1 beginnen die Daten nicht in Spalte U.
Ich vermute die Grundvoraussetzung wäre dass du mit richtigen Daten (Datentyp Datum) arbeitest, weil Daten als Text-String in unterschiedlicher Formatierung abzugleichen wird nichts.
cu
Chris
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 11:03:06
Daniel
Hi Chris,
habe es nun so gelöst:
'Datum 12
WkSh_Quelle.Range("AF1").Copy
WkSh_Ziel.Range("R1").PasteSpecial Paste:=xlPasteValues
'Löschen des Werts PBD-
Columns("G:R").Replace What:="PBD-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Wichtig ist das das Datumsformat in der Zelle vorher richtig festgelegt ist, dann klappt es auch.
Was nun allerdings etwas umständlich ist, ist das ich für jedes Datum die Auflistung mache - geht sicherlich auch effektiver.
Also
'Datum 1
-Code-
'Datum 2
-Code-
'Datum 3
-Code-
...
Als nächstes kommt die Ermittlung des Wochentags aus dem Datum das z.B. in G1 steht dran und soll dann in G3 ausgegeben werden.
Sorry, muss mich auch korrigieren:
Quelle : Tabelle 1
Ziel: Import_Sheet
Datum beginnt ab Stelle S in Quelle und soll an Ziel Tabelle ab Stelle G geschrieben werden.
Grüße
Daniel
Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 11:10:22
ChrisL
Hi Daniel
Wie gesagt, wenn in G1 ein richtiges Datum steht, dann musst du es für den Wochentag nur als TTTT formatieren.
Wie sieht nun folgender Code konkret aus?
'Datum 1
-Code-
'Datum 2
-Code-
'Datum 3
-Code-
cu
Chris
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 11:14:46
Daniel
Hi Chris, korrekt - das Datums-Format habe ich vorher angepasst, dann wird alles korrekt ausgegeben.
Der Code ist wie folgt aufgebaut (bitte störe dich nicht an den Spalten- / Zellen-Werten)
Sub DatumKopieren()
Dim WkSh_Quelle As Worksheet
Dim WkSh_Ziel As Worksheet
Application.ScreenUpdating = False
Set WkSh_Quelle = Worksheets("Tabelle1") ' das Quell-Tabellenblatt
Set WkSh_Ziel = Worksheets("Tabelle3") ' das Ziel-Tabellenblatt
'Datum 1
WkSh_Quelle.Range("U1").Copy
WkSh_Ziel.Range("G1").PasteSpecial Paste:=xlPasteValues
'Datum 2
WkSh_Quelle.Range("V1").Copy
WkSh_Ziel.Range("H1").PasteSpecial Paste:=xlPasteValues
'Datum 3
WkSh_Quelle.Range("W1").Copy
WkSh_Ziel.Range("I1").PasteSpecial Paste:=xlPasteValues
'Löschen des Werts PBD-
Columns("G:R").Replace What:="PBD-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 11:19:27
ChrisL
OK, warum kopierst du die Daten denn einzeln. Ginge doch auch...
WkSh_Quelle.Range("U1:W1").Copy
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 11:25:57
Daniel
Ah natürlich, manchmal denkt man wirklich zu kompliziert. Muss mich ins VBA erst wieder einarbeiten, gefühlt 10 Jahre nichts mehr damit gemacht.
Den Wochentag habe ich nun mit folgender Formel ermittelt, wie würde das denn in VBA aussehen:
=TEXT(G1;"TTTT")
Danke dir.
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 12:45:31
ChrisL
Ginge auch mit
=G1
und entsprechender Formatierung
Oder
Range("G3") = Format(Range("G1"), "DDDD")
cu
Chris
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 14:44:07
Daniel
Hi Chris,
danke dir!
Gibt es zu der Ermittlung des Wochentages noch eine schönere Variante, ähnlich wie bei dem Thema vorher?
'Wochentag ermitteln
Range("G3") = Format(Range("G1"), "DDDD")
Range("H3") = Format(Range("H1"), "DDDD")
Range("I3") = Format(Range("I1"), "DDDD")
Range("J3") = Format(Range("J1"), "DDDD")
Range("K3") = Format(Range("K1"), "DDDD")
Range("L3") = Format(Range("L1"), "DDDD")
Range("M3") = Format(Range("M1"), "DDDD")
Range("N3") = Format(Range("N1"), "DDDD")
Range("O3") = Format(Range("O1"), "DDDD")
Range("P3") = Format(Range("P1"), "DDDD")
Range("Q3") = Format(Range("Q1"), "DDDD")
Range("R3") = Format(Range("R1"), "DDDD")
Was auch noch auffällig ist gebe ich nur "DD" an, erhalte ich lediglich eine Zahl zurück, obwohl ich das Format vorher über die Formatierung (TT) definiert habe.
Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
08.08.2017 15:03:32
ChrisL
Hier zwei Varianten. Wie gesagt, ich würde das Datum als Datum sein lassen und nur die Formatierung definieren.
Wenn du Format Mo Di etc. haben willst, dann "DDD" (3fach, weil 2fach eben dem Nummernformat entspricht)
Sub t()
With Range("G3:R3")
.Formula = "=TEXT(G1,""TTT"")"
.Value = .Value
End With
End Sub

Sub tt()
Range("G3:R3") = Range("G1:R1").Value
Range("G3:R3").NumberFormat = "ddd"
End Sub

AW: mehrere Spalte kop. in vorh. Formatierung einfügen
09.08.2017 14:46:44
Daniel
Hi Chris,
du hast mir sehr geholfen, danke dir!
Wie bekomme ich denn nun die Werte unter den Datumsfeldern (S-AD) nun noch in mein Import_Sheet (A4 bis R4 und alles darunter natürlich). Er soll ja nur die Werte kopieren die auch wirklich vorhanden sind.
Damit funktioniert es zwar, geht sicher aber auch besser.
Kann man am Ende dann noch die Zellen ab Spalte A4 abwärts noch entsprechend verbinden, wenn der gleiche Wert vorhanden ist ?
'Kopiert die restlichen Werte unter das jeweilige
Sub DatumWerteKopieren()
Dim WkSh_Quelle As Worksheet
Dim WkSh_Ziel As Worksheet
Application.ScreenUpdating = False
Set WkSh_Quelle = Worksheets("Tabelle1") ' das Quell-Tabellenblatt
Set WkSh_Ziel = Worksheets("Tabelle4") ' das Ziel-Tabellenblatt
'Datum 1-12
WkSh_Quelle.Range("V2:AG900").Copy
WkSh_Ziel.Range("G4:R900").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Danke dir.
Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
09.08.2017 16:00:18
ChrisL
Hi Daniel
Deine Variante ist eigentlich OK, aber hier noch die Lösung nach dem gleichen Muster wie die Ursprungsfrage.
Public Sub Kopieren()
Dim WkSh_Quelle As Worksheet
Dim WkSh_Ziel As Worksheet
Dim rZelle As Range
Dim aUeberschr As Variant
Dim iIndx As Integer
Dim iSpalte As Integer
Dim lZeile As Long
aUeberschr = Array("Arbeitsplatz", "Material", "Bezeichnung", "Abladestelle")
Application.ScreenUpdating = False
Set WkSh_Quelle = Worksheets("Tabelle1") ' das Quell-Tabellenblatt
Set WkSh_Ziel = Worksheets("Import_Sheet") ' das Ziel-Tabellenblatt
With WkSh_Quelle
For iIndx = 0 To UBound(aUeberschr)
Set rZelle = .Rows(1).Find(aUeberschr(iIndx), LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
iSpalte = iSpalte + 1
lZeile = .Cells(Rows.Count, rZelle.Column).End(xlUp).Row
WkSh_Ziel.Range(WkSh_Ziel.Cells(4, iSpalte), WkSh_Ziel.Cells(lZeile + 2, iSpalte)) = _
.Range(.Cells(2, rZelle.Column), .Cells(lZeile, rZelle.Column)).Value
WkSh_Ziel.Range(WkSh_Ziel.Cells(4, 7), WkSh_Ziel.Cells(lZeile + 2, 18)) = _
.Range(.Cells(2, 19), .Cells(lZeile, 30)).Value
End If
Next iIndx
End With
WkSh_Ziel.Rows("4:4").Copy
WkSh_Ziel.Rows("5:" & lZeile + 2).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Zellen verbinden würde ich auf keinen Fall. Anschliessende Auswertungen sind dann kaum mehr möglich und der VBA Code könnte auch Probleme machen.
Falls doch... Soll denn nur in Spalte A verbunden werden?
cu
Chris
Anzeige
AW: mehrere Spalte kop. in vorh. Formatierung einfügen
10.08.2017 08:16:59
ChrisL
Hi
Kleiner Schönheitsfehler. Die betreffende Zeile steht sinnvollerweise ausserhalb von der For-Next Schleife.
Public Sub Kopieren()
Dim WkSh_Quelle As Worksheet
Dim WkSh_Ziel As Worksheet
Dim rZelle As Range
Dim aUeberschr As Variant
Dim iIndx As Integer
Dim iSpalte As Integer
Dim lZeile As Long
aUeberschr = Array("Arbeitsplatz", "Material", "Bezeichnung", "Abladestelle")
Application.ScreenUpdating = False
Set WkSh_Quelle = Worksheets("Tabelle1") ' das Quell-Tabellenblatt
Set WkSh_Ziel = Worksheets("Import_Sheet") ' das Ziel-Tabellenblatt
With WkSh_Quelle
For iIndx = 0 To UBound(aUeberschr)
Set rZelle = .Rows(1).Find(aUeberschr(iIndx), LookAt:=xlWhole, LookIn:=xlValues)
If Not rZelle Is Nothing Then
iSpalte = iSpalte + 1
lZeile = .Cells(Rows.Count, rZelle.Column).End(xlUp).Row
WkSh_Ziel.Range(WkSh_Ziel.Cells(4, iSpalte), WkSh_Ziel.Cells(lZeile + 2, iSpalte)) = _
.Range(.Cells(2, rZelle.Column), .Cells(lZeile, rZelle.Column)).Value
End If
Next iIndx
    WkSh_Ziel.Range(WkSh_Ziel.Cells(4, 7), WkSh_Ziel.Cells(lZeile + 2, 18)) = _
.Range(.Cells(2, 19), .Cells(lZeile, 30)).Value
End With
WkSh_Ziel.Rows("4:4").Copy
WkSh_Ziel.Rows("5:" & lZeile + 2).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

314 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige