Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1880to1884
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
copy+paste in andere Datei
20.04.2022 17:17:47
Sabine
Ola!
Habe mal wieder ein Problem - wahrscheinlich ein richtig peinliches Grundlagenproblem - und nachdem ich nun zwei...Korrektur: drei Stunden versucht habe, es zu lösen - ohne Erfolg - dachte ich mir, wende ich mich mal an die Profis.
Folgendes liegt an:
Zwei Dateien. Jeweils Bilanzwerte.
Die Datei Bilanz enthält u.a. das Blatt "Bilanz nach §266 HGB WT-1". Dieses kommt aus unserer Software und enthält Bilanzdaten pro Kostenart (KSA) für das Gesamtjahr und die einzelnen Perioden.
Die Datei TestMakroBilanz enthält ein Blatt, das normalerweise in einer größeren Datei enthalten ist. In diese sollen die Werte aus der Datei Bilanz hereinkopiert werden.
Mit copy + paste geht das aber nicht einfach, da das Blatt aus der Datei TestMakroBilanz wesentlich mehr KSA enthält. Hintergrund: Die Software spuckt für das Jahr immer nur bebuchte KSA aus und nicht jene, die auch in der Vergangenheit, also der nicht betrachteten Periode, bebucht worden.
Soweit klar?
Ich möchte in der Regel den vergangenen Monat kopieren + einfügen.
Was ich nun versucht habe per Makro zu hinterlegen:
1) Jahr eingeben via Inputbox
2) Monat eingeben via Inputbox
dadurch die Spalte finden, deren Werte kopiert und in TestMakroBilanz eingefügt werden sollen (Finde Wert Jahr in Zeile 1 und gehe den Monatswert nach rechts)
3) Spalte in der zu kopierenden Datei finden (können auch mehrere Jahre rausgezogen werden)
4) Spalte in der einzufügenden Datei finden (enthält hier im Bsp. mehr Jahre und deshalb Spalten)
5) Spalte A (=KSA) aus Datei Bilanz nutzen, um dadurch das Pendant in der anderen Datei zu finden, analog summewenn oder index(vergleich)
6) ist die KSA nicht in der einzufügenden Datei --> Msgbox mit Fehlermeldung und weiter machen
7) Zum Schluss die Info: Bin fertig.
Die eine Datei:
https://www.herber.de/bbs/user/152550.xlsx
die andere:
https://www.herber.de/bbs/user/152551.xlsm
Nicht wundern, die Werte ergeben natürlich keinen Sinn mehr.
Mein Code lautet nun:

Sub Bilanz_Daten_ziehen()
Dim EingabeJahr As Integer
Dim EingabeMonat As Integer
Dim SpalteCopy As Integer
Dim SpaltePaste As Integer
Dim ZeileCopy As Integer
Dim ZeilePaste As Integer
Dim KSA As String
Dim Wert As Integer
Do Until EingabeJahr  ""
EingabeJahr = Application.InputBox("Bitte geben Sie das Jahr ein" & vbCr & "Format: 20xx", "Jahr", "Bsp.: 2022", Type:=1)
Loop
Do Until EingabeMonat > 0 And EingabeMonat 
Wahrscheinlich wieder viel zu kompliziert und fehleranfällig - vllt hat ja jemand ne bessere Variante.
Bei mir hakt es gerade bei dem Punkt: If Not SpalteCopy Is Nothing Then
"Fehler beim Kompilieren. Typen unverträglich."
Wahrscheinlich irgendwas mit Range oder Wert aber was anderes muss her.... .
Keine Ahnung. Habe schon total viel versucht.
Vllt. weiß ja jemand eine andere Lösung...habe ich das bereits gesagt?
Sollten Fragen offen sein, bitte genau und narrensicher stellen - sonst sind "falsche" Antworten vorprogrammiert...Erfahrungswert.
Lieben Dank und lieben Gruß,
Sabi

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: copy+paste in andere Datei
20.04.2022 17:30:02
onur
Statt

Dim SpalteCopy As Integer
das

Dim SpalteCopy As Range
und statt

SpalteCopy = Cells("A1").End(xlToRight).Find.....
das

Set SpalteCopy = Cells("A1").End(xlToRight).Find...
Danach werden die nächsten Fehlermeldungen auftauchen.
AW: copy+paste in andere Datei
21.04.2022 08:17:18
Sabine
Hallo Onur,
danke für die Tipps.
Ich arbeite sie ein und sehe weiter.
Mir ist die Nacht auch eingefallen, dass ich es sowieso anders machen muss.
Statt nur einen Monat muss ich das ganze Jahr raus ziehen, sonst macht es ja keinen Sinn. Das bedeutet allerdings, dass ich vielleicht mit diesem resize arbeiten muss, was ich allerdings Null verstehe...
Na ja, heute Nachmittag habe ich wahrscheinlich Zeit mich daran zu setzen und dann melde ich mich sicherlich spätestens morgen wieder. :D Ist ja logisch, dass ich das dann erst recht nicht hinbekomme....aber Versuch macht...und davon kann ich was gebrauchen.
LG
Sabi.
Anzeige
AW: copy+paste in andere Datei
22.04.2022 10:19:55
Sabine
Ola,
ich habe meinen Code jetzt umgeschrieben.
Wie bereits erwähnt muss ich den Monat nicht suchen, sondern das komplette Jahr einfügen, inkl. Summenspalte, die vorne ist.
Der Code lautet nun wie folgt:

Sub Bilanz_Daten_ziehen()
Dim EingabeJahr As Integer
Dim SpalteCopy As Range
Dim SpaltePaste As Range
Dim ZeileCopy As Integer
Dim ZeilePaste As Range
Dim KSA As String
Dim Werte As Integer
Do Until EingabeJahr  ""
EingabeJahr = Application.InputBox("Bitte geben Sie das Jahr ein" & vbCr & "Format: 20xx", "Jahr", "Bsp.: 2022", Type:=1)
Loop
Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1").Activate
Set SpalteCopy = Cells("A1").End(xlToRight).Find(what:=EingabeJahr, After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not SpalteCopy Is Nothing Then
SpalteCopy = SpalteCopy.Resize(, SpalteCopy.Columns.Count + 14)
ThisWorkbook.Sheets("Input_Bilanz").Activate
Set SpaltePaste = Cells("A1").End(xlToRight).Find(what:=EingabeJahr, After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not SpaltePaste Is Nothing Then
SpaltePaste = SpaltePaste.Resize(, SpaltePaste.Columns.Count + 14)
ZeileCopy = 3
Do Until Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1").Range("A1").End(xlDown)
Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1").Activate
KSA = Cells(ZeileCopy, 1).Value 'bei Zelle A3 beginnen
Werte = Range(Cells(ZeileCopy, SpalteCopy.Column), Cells(ZeileCopy, SpalteCopy.Columns(SpalteCopy.Columns.Count).Column)).Value
ThisWorkbook.Sheets("Input_Bilanz").Activate
Set ZeilePaste = Cells("A1").End(xlDown).Find(what:=KSA, After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not ZeilePaste Is Nothing Then
Range(Cells(ZeilePaste.Rows(1), SpaltePaste.Column), Cells(ZeilePaste.Rows(1), SpaltePaste.Columns(SpaltePaste.Columns.Count).Column)).Value = Werte
ZeileCopy = ZeileCopy + 1
Else: MsgBox ("KSA nicht vorhanden. Bitte hinzufügen: " & KSA)
ZeileCopy = ZeileCopy + 1
Loop
MsgBox "Fertig. "
End Sub
Jetzt erhalte ich einen Fehler bei Loop. Fehler beim Kompilieren: Loop ohne Do. Aber das Do Until steht ja oben...hat jemand einen Rat? Wahrscheinlich Reihenfolge wieder falsch...
Zudem weiß ich nicht was besser ist - so wie es jetzt steht mit =Werte oder lieber .copy und .paste?
Statt dieser langen Zeile

Range(Cells(ZeilePaste.Rows(1), SpaltePaste.Column), Cells(ZeilePaste.Rows(1), SpaltePaste.Columns(SpaltePaste.Columns.Count).Column)).Value
könnte man vllt. auch besser resize nutzen? So wie oben? Wobei ich nicht mal weiß ob das oben richtig ist, da ich den Code noch nicht probieren konnte - aufgrund der Fehlermeldungen...
Vielleicht hat ja jemand Vorschläge.
Was der Code tun soll:
1) Jahr eingeben
2) Nach Jahr in "Bilanz" suchen, um die richtigen Spalten zu finden (wobei die Spaltenlänge in Summe 15 Spalten sind, deshalb +14)
3) das gleiche in der anderen Datei, wo der Code drinnen ist
4) die Kostenarten nacheinander kopieren aus Bilanz und einfügen in die andere Datei, bis runter
5) Sollte entsprechende KSA nicht vorhanden sein, dann Meldung
6) Zum Schluss Meldung fertig
Wahrscheinlich macht ein Screenupdating=False noch Sinn, damit nicht alles mit angeschaut werden muss.
Ich danke euch.
LG
Sabi
Anzeige
AW: copy+paste in andere Datei
22.04.2022 10:59:16
Sabine
Update:
Habe jetzt was am Code geändert und mehrere End if eingefügt. Habe die ganz vergessen.
Jetzt kommt keine Fehlermeldung mehr.
Mal schauen ob er macht was ich vorhabe.

Sub Bilanz_Daten_ziehen()
Dim EingabeJahr As Integer
Dim SpalteCopy As Range
Dim SpaltePaste As Range
Dim ZeileCopy As Integer
Dim ZeilePaste As Range
Dim KSA As String
Dim Werte As Integer
Do Until EingabeJahr  ""
EingabeJahr = Application.InputBox("Bitte geben Sie das Jahr ein" & vbCr & "Format: 20xx", "Jahr", "Bsp.: 2022", Type:=1)
Loop
Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1").Activate
Set SpalteCopy = Cells("A1").End(xlToRight).Find(what:=EingabeJahr, After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not SpalteCopy Is Nothing Then
SpalteCopy = SpalteCopy.Resize(, SpalteCopy.Columns.Count + 14)
Else: MsgBox ("Jahr kann nicht gefunden werden in Sage Bilanz")
End If
ThisWorkbook.Sheets("Input_Bilanz").Activate
Set SpaltePaste = Cells("A1").End(xlToRight).Find(what:=EingabeJahr, After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not SpaltePaste Is Nothing Then
SpaltePaste = SpaltePaste.Resize(, SpaltePaste.Columns.Count + 14)
Else: MsgBox ("Jahr kann nicht gefunden werden in FC-Datei")
End If
ZeileCopy = 3
Do Until Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1").Range("A1").End(xlDown)
Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1").Activate
KSA = Cells(ZeileCopy, 1).Value 'bei Zelle A3 beginnen
Werte = Range(Cells(ZeileCopy, SpalteCopy.Column), Cells(ZeileCopy, SpalteCopy.Columns(SpalteCopy.Columns.Count).Column)).Value
ThisWorkbook.Sheets("Input_Bilanz").Activate
Set ZeilePaste = Cells("A1").End(xlDown).Find(what:=KSA, After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not ZeilePaste Is Nothing Then
Range(Cells(ZeilePaste.Rows(1), SpaltePaste.Column), Cells(ZeilePaste.Rows(1), SpaltePaste.Columns(SpaltePaste.Columns.Count).Column)).Value = Werte
ZeileCopy = ZeileCopy + 1
Else: MsgBox ("KSA nicht vorhanden. Bitte hinzufügen: " & KSA)
End If
ZeileCopy = ZeileCopy + 1
Loop
MsgBox "Fertig. "
End Sub
Die anderen oben gestellten Fragen bestehen allerdings nach wie vor. :D
Danke.
Anzeige
AW: copy+paste in andere Datei
22.04.2022 13:22:52
Sabine
Hallo,
habe den Code jetzt nach einigen neuen Fehlermeldungen abgeändert.
Derzeit sieht es so aus:

Sub Bilanz_Daten_ziehen()
Dim EingabeJahr As Variant
Dim SpalteCopy As Range
Dim SpaltePaste As Range
Dim ZeileCopy As Long
Dim ZeilePaste As Range
Dim KSA As String
Dim Werte As Range
Dim ZeilenlängeCopy As Long
Do Until EingabeJahr  ""
EingabeJahr = Application.InputBox(prompt:="Bitte geben Sie das Jahr ein" & vbCr & "Format: 20xx", Title:="Jahr", Default:="Bsp.: 2022", Type:=1)
Loop
Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1").Activate
Range("A1").Select
Set SpalteCopy = Range(Selection, Selection.End(xlToRight)).Find(what:=EingabeJahr, After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not SpalteCopy Is Nothing Then
SpalteCopy.Resize(, SpalteCopy.Columns.Count + 14).Select
Else: MsgBox ("Jahr kann nicht gefunden werden in Sage Bilanz")
End If
ThisWorkbook.Sheets("Input_Bilanz").Activate
Range("E1").Select
Set SpaltePaste = Range(Selection, Selection.End(xlToRight)).Find(what:=EingabeJahr, After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not SpaltePaste Is Nothing Then
SpaltePaste.Resize(, SpaltePaste.Columns.Count + 14).Select
Else: MsgBox ("Jahr kann nicht gefunden werden in FC-Datei")
End If
ZeileCopy = 3
ZeilenlängeCopy = Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1").Cells(Rows.Count, 1).End(xlUp).Row 'Spalte A Anzahl Zeilen bis letzte
Do Until ZeileCopy = ZeilenlängeCopy + 1
Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1").Activate
KSA = Cells(ZeileCopy, 1).Value 'bei Zelle A3 beginnen
Range(Cells(ZeileCopy, SpalteCopy.Column), Cells(ZeileCopy, SpalteCopy.Columns(SpalteCopy.Columns.Count).Column)).Copy
ThisWorkbook.Sheets("Input_Bilanz").Activate
Range("D3").Select
Set ZeilePaste = Range(Selection, Selection.End(xlDown)).Find(what:=KSA, After:=ActiveCell, LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not ZeilePaste Is Nothing Then
Range(Cells(ZeilePaste.Rows(1), SpaltePaste.Column), Cells(ZeilePaste.Rows(1), SpaltePaste.Columns(SpaltePaste.Columns.Count).Column)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Else: MsgBox ("KSA nicht vorhanden. Bitte hinzufügen: " & KSA)
End If
ZeileCopy = ZeileCopy + 1
Loop
MsgBox "Fertig. "
End Sub
Jetzt hänge ich bei

Range(Cells(ZeilePaste.Rows(1), SpaltePaste.Column), Cells(ZeilePaste.Rows(1), SpaltePaste.Columns(SpaltePaste.Columns.Count).Column)).Select
. Er sagt "Typen unverträglich". Dabei habe ich beide Objekte? als Range zugeordnet. Jetzt weiß ich nicht mehr weiter.
An dem resize habe ich auch was geändert, sonst hat er mir immer die Zelle gelöscht.
Zudem habe ich copy + paste eingefügt, weil das mit dem =Wert auch zu einem Fehler geführt hat.
Kann es allerdings sein, dass der die Kopierung durch das neue Selektieren und suchen+finden wieder aufhebt? Dann wäre =Wert vielleicht doch besser, allerdings hatte ich da wieder einen Fehler...oh je.
Ich glaube ich krieg das Pferd nicht geschaukelt.
Anzeige
AW: copy+paste in andere Datei
22.04.2022 15:38:42
GerdL
Hallo Sabine,
xlFormulas2 kennt mein altes Excel nicht.

Sub Pferdeschaukel()
Dim EingabeJahr As Variant
Dim Quellblatt As Worksheet: Set Quellblatt = Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1")
Dim Ueberschrift As Range
Dim Jahresspalte As Long
Dim ZeileCopy As Long
Dim LetzteZeile As Long
Dim KSA As String
Dim Zielblatt As Worksheet: Set Zielblatt = ThisWorkbook.Sheets("Input_Bilanz")
Dim AusgabeJahr As Range
Dim Ausgabespalte As Long
Dim KSAZiel As Range
Do Until EingabeJahr  ""
EingabeJahr = Application.InputBox(prompt:="Bitte geben Sie das Jahr ein" & vbCr & "Format: 20xx", Title:="Jahr", _
Default:="Bsp.: 2022", Type:=1)
Loop
'Jahresspalte in Bilanz ermitteln
With Quellblatt
Set Ueberschrift = .Rows(1).Find(what:=EingabeJahr, After:=.Range("A1"), LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With
If Ueberschrift Is Nothing Then MsgBox ("Jahr kann nicht gefunden werden in Sage Bilanz"): Exit Sub
Jahresspalte = Ueberschrift.Column
With Zielblatt
'Ausgabespalte im Jahresblatt ermitteln
Set AusgabeJahr = .Range(.Range("E1"), .Range("E1").End(xlToRight)).Find(what:=EingabeJahr, After:=.Range("E1"), _
LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If AusgabeJahr Is Nothing Then MsgBox ("Jahr kann nicht gefunden werden in FC-Datei"): Exit Sub
Ausgabespalte = AusgabeJahr.Column
'Prüfen und Kopieren
ZeileCopy = 3
LetzteZeile = Quellblatt.Cells(Quellblatt.Rows.Count, 1).End(xlUp).Row 'Spalte A
Do
KSA = Quellblatt.Cells(ZeileCopy, 1).Value 'bei Zelle A3 beginnen
Set KSAZiel = .Columns("D").Find(what:=KSA, After:=.Range("D3"), LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not KSAZiel Is Nothing Then
Quellblatt.Cells(ZeileCopy, Jahresspalte).Resize(1, 14).Copy
.Cells(KSAZiel.Row, Ausgabespalte).PasteSpecial Paste:=xlPasteValues
Else
MsgBox ("KSA nicht vorhanden. Bitte hinzufügen: " & KSA)
End If
ZeileCopy = ZeileCopy + 1
Loop Until ZeileCopy = LetzteZeile + 1
End With
MsgBox "Fertig. "
'Objektvariablen zurücksetzen
Set KSAZiel = Nothing: Set AusgabeJahr = Nothing: Set Ueberschrift = Nothing: Set Quellblat = Nothing: Set Zielblatt = Nothing
End Sub

Gruß Gerd
Anzeige
AW: copy+paste in andere Datei
22.04.2022 16:20:56
Sabine
Hallo Gerd,
hat super funktioniert, vielen Dank!
Das einzige, das ich angepasst habe, weil ich mir unsicher war, ob das im Code enthalten ist, ist, dass ich vor ".Cells(KSAZiel.Row, Ausgabespalte).PasteSpecial Paste:=xlPasteValues" noch "Zielblatt" gesetzt habe.
Um sicher zu gehen.
Jetzt ist mir aufgefallen, dass manche KSA doppelt vorkommen, d.h. ich werde jetzt versuchen einzubauen, dass die Suchzeile in der Zieldatei, welche ja im .Find definiert sein müsste, immer die ist, bei der er zuletzt war. D.h. er soll nicht jedes Mal von Zeile 3 aus suchen, sondern eben Zeile x.
Mal sehen ob ich das schaffe. :D
Ich danke dir und wünsche dir ein schönes Wochenende!
LG
Sabi.
Anzeige
AW: copy+paste in andere Datei
25.04.2022 13:13:55
Sabine
Hallo Gerd,
ich hoffe du hattest ein schönes Wochenende.
Das Wetter hier war gut.
Ich habe ja bereits angedroht, dass ich noch etwas ändern möchte in dem Code.
Habe ich nun seit heute früh probiert, aber wie zu erwarten war, habe ich es leider nicht hinbekommen. Pferd schaukelt nicht mehr.
Ich habe deinen Code folgendermaßen vergewaltigt:

Sub Bilanzdaten_ziehen()
Dim EingabeJahr As Variant
Dim Quellblatt As Worksheet
Dim Ueberschrift As Range
Dim Jahresspalte As Long
Dim ZeileCopy As Long
Dim LetzteZeile As Long
Dim KSA As String
Dim Zielblatt As Worksheet: Set Zielblatt = ThisWorkbook.Sheets("Input_Bilanz")
Dim AusgabeJahr As Range
Dim Ausgabespalte As Long
Dim KSAZiel As Range
Dim KSAFindSpalte As Long
Dim KSAFindZeile As Long 'soll immer eine Zeile nach unten rutschen beim suchen, da KSA doppelt vorkommen (sonst würde er immer nur die erste nehmen, die gefunden wird)
Dim KSAFindletzteZeile As Long
Workbooks.Open Filename:="P:\xxx\Bilanz.xlsx", UpdateLinks:=0
Set Quellblatt = Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1")
Do Until EingabeJahr  ""
EingabeJahr = Application.InputBox(prompt:="Bitte geben Sie das Jahr ein" & vbCr & "Format: 20xx", Title:="Jahr", _
Default:="Bsp.: 2022", Type:=1)
Loop
'Jahresspalte in Bilanz ermitteln
With Quellblatt
Set Ueberschrift = .Rows(1).Find(what:=EingabeJahr, After:=.Range("A1"), LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With
If Ueberschrift Is Nothing Then MsgBox ("Jahr kann nicht gefunden werden in Sage Bilanz"): Exit Sub
Jahresspalte = Ueberschrift.Column
With Zielblatt
'Ausgabespalte im Jahresblatt ermitteln
Set AusgabeJahr = .Range(.Range("E1"), .Range("E1").End(xlToRight)).Find(what:=EingabeJahr, After:=.Range("E1"), _
LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If AusgabeJahr Is Nothing Then MsgBox ("Jahr kann nicht gefunden werden in FC-Datei"): Exit Sub
Ausgabespalte = AusgabeJahr.Column
'Prüfen und Kopieren
ZeileCopy = 3
LetzteZeile = Quellblatt.Cells(Quellblatt.Rows.Count, 1).End(xlUp).Row 'Spalte A
KSAFindSpalte = 4 'Spalte D
KSAFindZeile = 3 'bei Zeile 3 beginnen
KSAFindletzteZeile = Zielblatt.Cells(Zielblatt.Rows.Count, KSAFindSpalte).End(xlUp).Row
Do
KSA = Quellblatt.Cells(ZeileCopy, 1).Value 'bei Zelle A3 beginnen
Set KSAZiel = Zielblatt.Range(Cells(KSAFindZeile, KSAFindSpalte), Cells(KSAFindletzteZeile, KSAFindSpalte)).Find(what:=KSA, After:=Zielblatt.Cells(KSAFindZeile, KSAFindSpalte), LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not KSAZiel Is Nothing Then
Quellblatt.Cells(ZeileCopy, Jahresspalte).Resize(1, 14).Copy
Zielblatt.Cells(KSAZiel.Row, Ausgabespalte).PasteSpecial Paste:=xlPasteValues
Else
MsgBox ("KSA nicht vorhanden. Bitte hinzufügen: " & KSA)
End If
ZeileCopy = ZeileCopy + 1
KSAFindZeile = KSAFindZeile + 1
Loop Until ZeileCopy = LetzteZeile
End With
MsgBox "Fertig. "
'Objektvariablen zurücksetzen
Set KSAZiel = Nothing: Set AusgabeJahr = Nothing: Set Ueberschrift = Nothing: Set Quellblatt = Nothing: Set Zielblatt = Nothing
End Sub
Jetzt kommt eine Fehlermeldung bei Set KSAZiel = Zielblatt.Range(Cells(KSAFindZeile, KSAFindSpalte), Cells(KSAFindletzteZeile, KSAFindSpalte)).Find(what:=KSA, After:=Zielblatt.Cells(KSAFindZeile, KSAFindSpalte), LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
mit dem Text

Die Methode Range für das Objekt _worksheet ist fehlgeschlagen
.
Heißt das, ich muss das Zielblatt wegnehmen vor dem .range(cells....))? Woher weiß er dann, dass er das Zielblatt nehmen muss? Da bin ich noch nicht so hinter gestiegen. Vielleicht ist der Grund aber auch anderer und hat mit der Dimensionierung zu tun? Ich weiß es nicht.
Danke dir und lieben Gruß,
Sabi
Anzeige
AW: copy+paste in andere Datei
25.04.2022 13:23:29
Sabine
Warte, mir ist gerade was eingefallen:
Unten statt
KSAFindZeile = KSAFindZeile + 1
steht nun
Set KSAFindZeile.Row = KSAZiel.Row
(ungetestet).
Das heißt die Zeile, wo er beginnen soll zu suchen, ist die, wo er das letzte Mal fündig geworden ist.
Wenn das so funktionieren sollte...
AW: copy+paste in andere Datei
25.04.2022 14:39:59
GerdL
Hallo Sabine,
innerhalb des With (Zielblatt) -- End With-Blocks weist der pure Punkt vor jedem Range und Cells auf dieses Blatt.
Auch ungetestet:

Sub PferdeschaukelI()
Dim EingabeJahr As Variant
Dim Quellblatt As Worksheet: Set Quellblatt = Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1")
Dim Ueberschrift As Range
Dim Jahresspalte As Long
Dim ZeileCopy As Long
Dim LetzteZeile As Long
Dim KSA As String
Dim erstFind As String
Dim Zielblatt As Worksheet: Set Zielblatt = ThisWorkbook.Sheets("Input_Bilanz")
Dim AusgabeJahr As Range
Dim Ausgabespalte As Long
Dim KSAZiel As Range
Do Until EingabeJahr  ""
EingabeJahr = Application.InputBox(prompt:="Bitte geben Sie das Jahr ein" & vbCr & "Format: 20xx", Title:="Jahr", _
Default:="Bsp.: 2022", Type:=1)
Loop
'Jahresspalte in Bilanz ermitteln
With Quellblatt
Set Ueberschrift = .Rows(1).Find(what:=EingabeJahr, After:=.Range("A1"), LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With
If Ueberschrift Is Nothing Then MsgBox ("Jahr kann nicht gefunden werden in Sage Bilanz"): Exit Sub
Jahresspalte = Ueberschrift.Column
With Zielblatt
'Ausgabespalte im Jahresblatt ermitteln
Set AusgabeJahr = .Range(.Range("E1"), .Range("E1").End(xlToRight)).Find(what:=EingabeJahr, After:=.Range("E1"), _
LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If AusgabeJahr Is Nothing Then MsgBox ("Jahr kann nicht gefunden werden in FC-Datei"): Exit Sub
Ausgabespalte = AusgabeJahr.Column
'Prüfen und Kopieren
ZeileCopy = 3
LetzteZeile = Quellblatt.Cells(Quellblatt.Rows.Count, 1).End(xlUp).Row 'Spalte A
For ZeileCopy = 3 To LetzteZeile
KSA = Quellblatt.Cells(ZeileCopy, 1).Value 'bei Zelle A3 beginnen
Set KSAZiel = .Columns("D").Find(what:=KSA, After:=.Range("D3"), LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If KSAZiel Is Nothing Then
MsgBox ("KSA nicht vorhanden. Bitte hinzufügen: " & KSA)
Else
ersteFund = KSAZiel.Address
Do
Quellblatt.Cells(ZeileCopy, Jahresspalte).Resize(1, 14).Copy
.Cells(KSAZiel.Row, Ausgabespalte).PasteSpecial Paste:=xlPasteValues
Set KSAZiel = .Columns("D").FindNext(KSA)
Loop Until KSAZiel.Address = ersteFund
End If
Next ZeileCopy
End With
MsgBox "Fertig. "
'Objektvariablen zurücksetzen
Set KSAZiel = Nothing: Set AusgabeJahr = Nothing: Set Ueberschrift = Nothing: Set Quellblatt = Nothing: Set Zielblatt = Nothing
End Sub
Gruß Gerd
Anzeige
AW: copy+paste in andere Datei
25.04.2022 15:06:48
Sabine
Hallo Gerd,
danke für die Antwort.
Dass die With-Funktion zur Folge hat, dass automatisch diese Datei benutzt wird, wenn nichts anderes definiert ist, habe ich nun mittlerweile auch verstanden. :D
Wenn ich den Code richtig interpretiere gehst du nun so vor, dass er alle KSA suchen soll mit dem Namen x und an jede Stelle den Wert y einfügen soll.
Ich glaube da habe ich mich unverständlich ausgedrückt.
Die Bilanz ist ja in Aktiv und Passiv eingeteilt. Manche KSA sind, warum auch immer, so in dem Bericht hinterlegt, dass sie sowohl auf der Aktiv, als auch Passivseite auftauchen, aber wertseitig nur an einer Stelle. Das heißt je nachdem ob Soll oder Haben des Kontos überwiegt, wird der Wert entweder auf der Aktiv- oder Passivseite ausgewiesen. Nicht aber auf beiden Seiten. Wenn ein Passivkonto bspw. auf der Aktivseite steht, dann ist dann ein Minus davor. Wäre es auf der Passivseite, steht es als positiver Wert dort.
Deshalb wollte ich auch so vorgehen, dass er zum Finden der KSA immer weiter herunter geht, beginnend dort, wo er zuletzt gefunden hat. Dann passt das wieder.
Das bedeutet dann, dass wenn er bspw. die KSA Umsatzsteuer bereits eingefügt hat, da sie auf der Aktivseite ebenfalls ist, dann danach auf der Passivseite einfügt, weil sie später kommt. Dann hat er entweder oben bereits eine Null eingefügt oder macht das nun unten auf der Passivseite.
Ich hoffe das war verständlich?
Ich habe noch etwas an dem Code herumgebastelt:

Sub Bilanzdaten_ziehen()
Dim EingabeJahr As Variant
Dim Quellblatt As Worksheet
Dim Ueberschrift As Range
Dim Jahresspalte As Long
Dim ZeileCopy As Long
Dim LetzteZeile As Long
Dim KSA As String
Dim Zielblatt As Worksheet: Set Zielblatt = ThisWorkbook.Sheets("Input_Bilanz")
Dim AusgabeJahr As Range
Dim Ausgabespalte As Long
Dim KSAZiel As Range
Dim KSAFindSpalte As Long
Dim KSAFindZeile As Long 'soll immer eine Zeile nach unten rutschen beim suchen, da KSA doppelt vorkommen (sonst würde er immer nur die erste nehmen, die gefunden wird)
Dim KSAFindletzteZeile As Range
Workbooks.Open Filename:="P:\10_Controlling\11_Beteiligungen\03_Willhelm_Technologies\FC_JA22\Bilanz.xlsx", UpdateLinks:=0
Set Quellblatt = Workbooks("Bilanz.xlsx").Sheets("Bilanz nach §266 HGB WT-1")
Do Until EingabeJahr  ""
EingabeJahr = Application.InputBox(prompt:="Bitte geben Sie das Jahr ein" & vbCr & "Format: 20xx", Title:="Jahr", _
Default:="Bsp.: 2022", Type:=1)
Loop
'Jahresspalte in Bilanz ermitteln
With Quellblatt
Set Ueberschrift = .Rows(1).Find(what:=EingabeJahr, After:=.Range("A1"), LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With
If Ueberschrift Is Nothing Then MsgBox ("Jahr kann nicht gefunden werden in Sage Bilanz"): Exit Sub
Jahresspalte = Ueberschrift.Column
With Zielblatt
'Ausgabespalte im Jahresblatt ermitteln
Set AusgabeJahr = .Range(.Range("E1"), .Range("E1").End(xlToRight)).Find(what:=EingabeJahr, After:=.Range("E1"), _
LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If AusgabeJahr Is Nothing Then MsgBox ("Jahr kann nicht gefunden werden in FC-Datei"): Exit Sub
Ausgabespalte = AusgabeJahr.Column
'Prüfen und Kopieren
KSAFindSpalte = 4 'Spalte D
KSAFindZeile = 3 'bei Zeile 3 beginnen
KSAFindletzteZeile = .Cells(.Rows.Count, KSAFindSpalte).End(xlUp).Row
ZeileCopy = 3
LetzteZeile = Quellblatt.Cells(Quellblatt.Rows.Count, 1).End(xlUp).Row 'Spalte A
Do
KSA = Quellblatt.Cells(ZeileCopy, 1).Value 'bei Zelle A3 beginnen
Set KSAZiel = Zielblatt.Range(Cells(KSAFindZeile, KSAFindSpalte), Cells(KSAFindletzteZeile, KSAFindSpalte)).Find(what:=KSA, After:=Zielblatt.Cells(KSAFindZeile, KSAFindSpalte), LookIn:=xlFormulas2, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not KSAZiel Is Nothing Then
Quellblatt.Cells(ZeileCopy, Jahresspalte).Resize(1, 14).Copy
.Cells(KSAZiel.Row, Ausgabespalte).PasteSpecial Paste:=xlPasteValues
Else
MsgBox ("KSA nicht vorhanden. Bitte hinzufügen: " & KSA)
End If
ZeileCopy = ZeileCopy + 1
KSAFindZeile = KSAZiel.Row
Loop Until ZeileCopy = LetzteZeile
End With
MsgBox "Fertig. "
'Objektvariablen zurücksetzen
Set KSAZiel = Nothing: Set AusgabeJahr = Nothing: Set Ueberschrift = Nothing: Set Quellblatt = Nothing: Set Zielblatt = Nothing
End Sub
Jetzt meckert er bei
" KSAFindletzteZeile = .Cells(.Rows.Count, KSAFindSpalte).End(xlUp).Row"
"Objektvariable oder With-Blockvariable nicht festgelegt"
Dabei habe ich doch sowohl KSAFindSpalte als auch KSAFindletzteZeile dimensioniert und KSAFindSpalte zuvor mit einem Wert versehen.
Soll ich noch einmal die Datei hochladen mit dem Code?
LG
Sabi
Anzeige
AW: copy+paste in andere Datei
25.04.2022 15:28:41
GerdL
Ja, wenn das Eigenkapitalkonto auf der Aktivseite steht, muss man zum Insolvenzverwalter. :-)
Was ich nicht weis, ist eine Kostenart ggf. in der Bilanzübersicht doppelt vorhanden oder im Jahresblatt doppelt?
Ich hatte im Jahresblatt angenommen.
Und welche Auswirkungen hat dies bei diesem Datenextrakt genau?
P.S. Bei "FindNext" war noch einen Fehler drin; da sollte zweimal KSAZiel stehen.
Gruß Gerd
AW: copy+paste in andere Datei
25.04.2022 16:52:11
Sabine
Hallo Gerd,
entschuldige die verspätete Antwort. Ich war spontan in einem Termin.
Also eine KSA kommt sowohl in der Bilanz (Ausgangsdatei) als auch in der TestMakro_Bilanz Datei (wo hineinkopiert wird) doppelt vor.
In der Ausgangsdatei steht dann bspw. bei Umsatzsteuer auf der Aktivseite Null in der gesamten Zeile für das Jahr und auf der Passivseite stehen die positiven Werte. Kann aber auch so sein, dass auf der Aktivseite die negativen Werte stehen und auf der Passivseite alle Werte für das Jahr sind Null. Dies kann sich nach einem Monat auch ändern, weil dann plötzlich der Saldo negativ oder positiv (je nachdem) ist.
Das gleiche mit Vorsteuer und leider auch Forderungen und Verbindlichkeiten. Don´t ask...
Ja und in der TestMakro Datei, wo hineinkopiert wird und eben auch die vergangenen Jahre stehen, dort ist es ebenfalls so. Ist dann eben auch der Fall, dass das Konto 2019 + 2020 auf der Aktivseite mit Werten befüllt war und 2021 + 2022 wiederum auf der Passivseite.
In der Bilanzdatei, von wo die Werte kommen, geht er ja sowieso die KSA von oben nach unten durch. Nur deshalb ist es auch wichtig, dass er das ebenfalls in der Zieldatei macht. Damit die Werte dann nicht sowohl auf der Aktiv- als auch Passivseite stehen - sei es ob nun Werte oder = 0.
D.h. eine KSA kann max. zwei Mal vorkommen. In beiden Dateien.
Wichtig ist deshalb auch, dass er meldet, wenn ein Konto zwar auf der Aktivseite steht, jedoch nicht auf der Passivseite, wo das Konto auf einmal gelandet ist und Werte stehen.
Ich hoffe ich habe deine Frage(n) damit beantwortet?
Auswirkungen sollten damit auch verständlich geworden sein?
PS.: Gesetz erlaubt auch negatives EK in gewissem Maße ;)
LG
Sabi.
AW: copy+paste in andere Datei
25.04.2022 17:27:55
GerdL
Hallo Sabine,
die Wahrheit u. Klarheit ist heutzutage leider komplex. :-)
Steht die Kostenart, wenn doppelt, dann in beiden Tabellen doppelt u. jeweils direkt untereinander?
Wenn eine "Umwandlung" (Aktiva/Passiva; + /-) erfolgen soll, nach welchen Kriterien?
Anhand der Nummernkreise für die Kostenarten?
Irgendwie fehlt mir die Vorstellungskraft.
Was passiert oder besser passt nicht wie gewollt, wenn du meinen letzten Code springen läßt?
Gruß Gerd
AW: copy+paste in andere Datei
26.04.2022 09:43:13
Sabine
Guten Morgen Gerd,
entschuldige meine späte Antwort. Jetzt muss ich mich langsam sputen, sonst wird der Thread archiviert bevor wir hier durch sind. :D
Ich habe heute früh die Bilanz angepasst um dir zu zeigen was ich meine und Excel teilt mir mit "gespeichert". Ich schließe die Datei und - Pustekuchen. Alles weg. Oi.
So, also folgendes.
Hier sind die beiden Dateien:
1) Bilanz (Daten aus dem System, die kopiert werden sollen)
https://www.herber.de/bbs/user/152669.xlsx
2) TestMakro-Bilanz (Datei, in der die Werte einzufügen sind)
https://www.herber.de/bbs/user/152670.xlsm
Konzentrieren wir uns auf das Jahr 2022 und als Beispiel auf das Konto 01600 - Verbindlichkeiten aus Lieferungen und Leistungen.
Ich habe dein Makro bereits durchlaufen lassen, damit du siehst, was passiert ist und erkläre dir, was ich vorhabe mit meinem Code.
Suchst du nun nach dem Konto 01600 in Bilanz, dann wirst du sehen, dass es in Zeile 39 vorkommt. Das ist die Aktivseite der Bilanz. Dort steht überall Null in den Zeilen. Das entspricht auch dem Grundsatz des Kontos, da es originär ein Passivkonto ist. Suchst du nun weiter findest du es erneut in Zeile 85. Das ist die Passivseite der Bilanz. Normalerweise ist die Bilanz in zwei Seiten aufgeteilt - links und rechts - das System schreibt die KSA und Werte aber untereinander. In Zeile 85 stehen dann auch Werte.
Dann schau mal in die TstMakro-Datei. Wenn du dort nach dem Konto 01600 suchst, findest du es auch zwei Mal, aber durch dein Makro immer mit Werten. Und zwar den Werten, dass das Makro das letzte Mal für das Konto 01600 gefunden hat. Das heißt die Werte aus der Passivseite. Die stehen nun sowohl in Zeile 45, als auch 97 (bitte auf 2022 schauen). Das darf aber nicht sein. In Zeile 45 müssten die Nullwerte stehen und in Zeile 97, so wie es jetzt richtig ist, die positiven Werte.
Ein anderes Beispiel ist die Vorsteuer - Konto 01576. Suchst du danach in Bilanz tauchen Werte auf der Aktivseite in Zeile 37 auf und Nullwerte in Zeile 95. Das ist ein Aktivkonto, d.h. so ist es auch normal. Wäre der Saldo mal, warum auch immer, negativ, stünde das Konto mit negativen Werten auf der Passivseite und die Aktivwerte wären Null. In der TestMakro-Datei stehen auf beiden Seiten der Bilanz unter dem Konto 01576 nun Nullwerte, da das die letzten Werte sind des Kontos , dass das Makro gefunden, kopiert und überall in TestMakro eingefügt hat unter der KSA.
Ein Tausch von Aktiv- und Passivkonten benötige ich nicht. Das muss das Makro nicht machen. Das wäre zu aufwendig. Das habe ich anderweitig gelöst.
Ich möchte nur, dass das Makro, was es auch bereits macht, von oben nach unten die KSA durchgeht, die Werte kopiert (bis hierhin funktioniert es) und dann beim Einfügen darauf achtet, dass er nicht jedes Mal die gesamte Spalte D nach den KSA durchsucht und die Werte einfügt, sondern immer dort mit der Suche beginnt, wo er das letzte Mal war. Dann fügt er, wenn das Makro zum Kopieren auf der Passivseite gelandet ist, auch nur auf der Passivseite die Werte ein und nicht auch weiter oben auf der Aktivseite.
LG
Sabi

106 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige