VBA Kopieren mit Wert und Format

Forumthread: VBA Kopieren mit Wert und Format

19.06.2018 23:04:23

Patric

Hallo zusammen,
ich hoffe ich habe im Forum meine Frage nicht übersehen und stelle Sie nicht doppelt.
Für meine wahrscheinlich für euch einfache Aufgabenstellung möchte ich mich schon einmal entschuldigen.
Ich möchte folgendes tun.
Ich habe ein Tabellenblatt, gefüllt mit Daten.
Nun möchte ich einen Bereich aus Tabellenblatt 1 in Tabellenblatt 2 Kopieren.
Aus Tabellenblatt 1 sollen immer weitere / neue Einträge in die jeweils erste leere Zeile aus Tabellenblatt 2 eingefügt werden.
Ich möchte aber gerne das Format behalten und ausschließlich die Werte kopieren. Geht das?
Mein Versuch, bei dem das Format vernünftig kopiert wird aber immer ein Laufzeitfehler für die Werte erscheint, sieht bisher so aus:
' KW_abschließen Makro


Sub KW_Abschluß_Klicken()
'Message Box generieren
If MsgBox("KW abschließen & zurücksetzen?", vbOKCancel) = vbOK Then
End If
'Bereich kopieren
Sheets("Wochenbericht").Range("A3:AO49").Copy
'einfügen in erste freie Zeile in ausgabe
Sheets("Jahresübersicht").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:= _
xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Sheets("Jahresübersicht").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:= _
xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Kopiermodus beenden
Application.CutCopyMode = False
End Sub

Der Laufzeitfehler 1004 sagt aus, dass alle verbundenen Zellen die selbe Größe haben müssen.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: VBA Kopieren mit Wert und Format

20.06.2018 07:39:31

fcs

Hallo Patric,
verbundene Zellen sind bei manchen Operation des Programmierers Alptraum - machen Schwierigkeiten erfordern zusätzlichen Programmieraufwand.
Deshalb: verbundene Zellen wenn möglich vermeiden!
Wenn du auf die verbundenen Zellen nicht verzichten kannst/willst, dann müssen die Spaltenblöcke mit verbundenen Zellen und nicht verbundenen Zellen jeweils getrennt kopiert werden.
Beispiel nach folgendem Schema.
Gruß
Franz

' KW_abschließen Makro
Sub KW_Abschluß_Klicken()
Dim Zeile As Long, Spalte As Long
Dim wksWo As Worksheet
Dim wksJahr As Worksheet
'Message Box generieren
If MsgBox("KW abschließen & zurücksetzen?", vbOKCancel) = vbCancel Then
Exit Sub
End If
Set wksWo = Sheets("Wochenbericht")
Set wksJahr = Sheets("Jahresübersicht")
Application.ScreenUpdating = False
With wksJahr
'erste freie Zeile in Ausgabe-Blatt in Übersicht
Zeile = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
'Bereich 1 kopieren
wksWo.Range("A3:T49").Copy
'einfügen in
Spalte = .Range("A3").Column
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Bereich 2 kopieren = 1. Spalten-Bereich mit verbundenen Zellen
wksWo.Range("U3:Y49").Copy
'einfügen in
Spalte = .Range("U3").Column
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Bereich 3 kopieren
wksWo.Range("Z3:AO49").Copy
'einfügen in
Spalte = .Range("Z3").Column
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
'Kopiermodus beenden
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

AW: VBA Kopieren mit Wert und Format

20.06.2018 11:28:26

Patric

Hallo Franz,
vielen Dank für deine Schnelle Antwort und die damit verbundene Arbeit.
Da ich keine verbundenen Zellen in meiner Tabelle gefunden habe, habe ich zum Test eines generellen Makrofehler dein Makro übernommen, erst einmal aber noch versucht den kompletten Bereich zu kopieren.
Ich werde das Makro mal anpassen um mit Spalte "A" anzufangen um zu schauen ob mir der Fehler bereits ab der 1. Spalte ausgeworfen wird.
Ich habe meine Beispieldatei mal angehangen. Vielleicht könntest du dennoch mal draufschauen um zu schauen ob ich ein generelles Problem in den Tabellen habe ?
Nochmals vielen Dank!!!
https://www.herber.de/bbs/user/122202.xlsm

AW: VBA Kopieren mit Wert und Format

20.06.2018 14:57:11

fcs

Hallo Partrik,
die 6 verbundenen Zellbereiche befinden sich im Zellbereich J3:N5.
Das macht es etwas komplizierter.
Hier müssen die Zellbereiche J3:K5, L3:N5, und J6:N49 getrennt übertragen werden, damit kein Fehler auftritt.

Sub KW_Abschluß_Klicken()
Dim Zeile As Long, Spalte As Long
Dim wksWo As Worksheet
Dim wksJahr As Worksheet
'Message Box generieren
If MsgBox("KW abschließen & zurücksetzen? TEST TEST", _
vbOKCancel) = vbCancel Then
Exit Sub
End If
Set wksWo = Sheets("Wochenbericht")
Set wksJahr = Sheets("Jahresübersicht")
Application.ScreenUpdating = False
With wksJahr
'erste freie Zeile in Ausgabe-Blatt in Übersicht
Zeile = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
'Bereich 1 kopieren
wksWo.Range("A3:I49").Copy
'einfügen in
Spalte = .Range("A3").Column
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Bereich 2 kopieren = 1. Spalten-Bereich mit verbundenen Zellen
wksWo.Range("J3:K5").Copy
'einfügen in
Spalte = .Range("J3").Column
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Bereich 3 kopieren = 1. Spalten-Bereich mit verbundenen Zellen
wksWo.Range("L3:N5").Copy
'einfügen in
Spalte = .Range("L3").Column
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Bereich 5 kopieren
wksWo.Range("J6:N49").Copy
'einfügen in
Spalte = .Range("J6").Column
.Cells(Zeile + 3, Spalte).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(Zeile + 3, Spalte).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Bereich 6 kopieren
wksWo.Range("O3:AO49").Copy
'einfügen in
Spalte = .Range("O3").Column
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Cells(Zeile, Spalte).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
'Kopiermodus beenden
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Hilfsmakro zur Suche von verbundenen Zellbereichen

Sub verbundene_Zellensuchen()
Dim wks As Worksheet
Dim rngZelle As Range
Set wks = ActiveSheet
For Each rngZelle In wks.UsedRange.Cells
If rngZelle.MergeCells = True Then
If MsgBox("Verbunden: " & rngZelle.MergeArea.Address(False, False, xlA1), _
vbOKCancel, "verbundene Zellen suchen") = vbCancel Then Exit For
End If
Next
End Sub

AW: VBA Kopieren mit Wert und Format

22.06.2018 10:13:36

Patric

Wollte nur noch einmal eine kurze Rückmeldung geben, es hat alles hervorragend funktioniert.
Die verbundenen Zellen muss ich komplett übersehen haben.
Vielen Dank für die schnelle Hilfe und deine Mühe!

Schritt-für-Schritt-Anleitung

Um in Excel VBA Werte und Formate zu kopieren, kannst du folgende Schritte befolgen:

Öffne den VBA-Editor: Drücke ALT + F11, um den Visual Basic for Applications (VBA) Editor zu öffnen.
Erstelle ein neues Modul: Rechtsklicke im Projekt-Explorer auf dein Arbeitsblatt und wähle „Einfügen“ > „Modul“.

Füge den folgenden Code ein:

Sub KW_Abschluß_Klicken()
   Dim Zeile As Long, Spalte As Long
   Dim wksWo As Worksheet
   Dim wksJahr As Worksheet

   ' Arbeitsblätter zuweisen
   Set wksWo = Sheets("Wochenbericht")
   Set wksJahr = Sheets("Jahresübersicht")

   ' Erste freie Zeile in Ausgabe-Blatt finden
   Zeile = wksJahr.Cells(wksJahr.Rows.Count, 1).End(xlUp).Row + 1

   ' Bereich kopieren und Formate übertragen
   wksWo.Range("A3:AO49").Copy
   wksJahr.Cells(Zeile, 1).PasteSpecial Paste:=xlPasteFormats
   wksJahr.Cells(Zeile, 1).PasteSpecial Paste:=xlPasteValues

   ' Kopiermodus beenden
   Application.CutCopyMode = False
End Sub

Führe das Makro aus: Schließe den VBA-Editor und kehre zu Excel zurück. Drücke ALT + F8, wähle das Makro aus und klicke auf „Ausführen“.

Häufige Fehler und Lösungen

Laufzeitfehler 1004: Dieser Fehler tritt häufig auf, wenn du versuchst, verbundene Zellen zu kopieren. Stelle sicher, dass du die verbundenen Zellen in separaten Bereichen behandelst, wie im Beispiel oben gezeigt.
Format wird nicht übertragen: Achte darauf, dass du zuerst die Formate mit xlPasteFormats und danach die Werte mit xlPasteValues überträgst.

Alternative Methoden

Wenn du keine VBA-Lösungen verwenden möchtest, kannst du auch die folgenden Alternativen in Betracht ziehen:

Manuelles Kopieren: Markiere den gewünschten Bereich, drücke Strg + C, gehe zum Zielblatt und verwende Rechtsklick > Inhalte einfügen > Werte und Formate.
Excel-Funktionen: Verwende Formeln wie =A1 für die Werteübertragung, allerdings wird damit das Format nicht übertragen. Eine Kombination aus Formeln und manuellem Formatieren kann ebenfalls eine Lösung sein.

Praktische Beispiele

Hier ist ein praktisches Beispiel, wie du mit VBA sowohl Werte als auch Formate kopieren kannst:

Sub BeispielKopieren()
    Dim wksQuelle As Worksheet
    Dim wksZiel As Worksheet
    Set wksQuelle = ThisWorkbook.Sheets("Quelle")
    Set wksZiel = ThisWorkbook.Sheets("Ziel")

    wksQuelle.Range("A1:B10").Copy
    wksZiel.Cells(1, 1).PasteSpecial Paste:=xlPasteFormats
    wksZiel.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
End Sub

In diesem Beispiel wird der Bereich A1:B10 von der Quelle in das Zielblatt kopiert, wobei sowohl das Format als auch die Werte erhalten bleiben.

Tipps für Profis

Vermeide verbundene Zellen: Wenn möglich, vermeide die Verwendung von verbundenen Zellen, da sie in VBA zu Problemen führen können.
Verwende Application.ScreenUpdating = False: Dies kann die Ausführungsgeschwindigkeit deines Makros erhöhen, indem es das Bildschirmupdate während der Ausführung des Makros deaktiviert.
Erstelle Hilfsmakros: Erstelle ein Makro, das dir hilft, verbundene Zellen zu finden, um Probleme während des Kopierens zu vermeiden.

FAQ: Häufige Fragen

1. Wie kann ich nur die Werte ohne Format kopieren?
Du kannst xlPasteValues verwenden, um nur die Werte zu kopieren, ohne das Format zu übertragen.

2. Was mache ich, wenn ich Laufzeitfehler erhalte?
Überprüfe deine Zellbereiche und stelle sicher, dass alle Zellen die gleiche Größe haben, insbesondere wenn du mit verbundenen Zellen arbeitest.

Beliebteste Forumthreads (12 Monate)

VBA Kopieren mit Wert und Format

Forumthread: VBA Kopieren mit Wert und Format

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial