Excel-VBA: Copy-Paste ohne Formatierung

Computer: Software & Programmierung Office Software
#1

Salu liebe Profis,

zwei Fragen zum Copy-Paste in VBA:

  • Folgendes funktioniert
    With Worksheets(„TabelleXY“)
    .Range(„20:30“).Copy
    .Range(„25:35“).Select
    .PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, IconFileName:=False
    End With

Während das hier nicht funktioniert:
With Worksheets(„TabelleXY“)
.Range(„20:30“).Copy
.Range(„25:35“).PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, IconFileName:=False
End With

Wie kann ich das Paradigma „vermeide select und activate“ hier umsetzen?

  • Wie kann ich mit Formeln (!) Copy-Pasten ohne daß ich die Formatierung mitkopiere?

Ganz lieben Dank und beste Grüßlichkeiten :o)
-Rob.

#2

Grüezi Rob

Wenn ich dich richtig verstehe (dazu äusserst Du dich nicht wirklich, das lese ich aus dem Titel des Beitrages und dem Code) willst Du nur die Werte in den neuen Bereich einfügen?

Wenn Du mit Copy/Paste arbeitest, dann geht da ausser .PaseSpecial nicht viel anderes.

Aber Du könntest den Spiess ja umdrehen und die Werte des neuen Bereiches gleich denen des ‚alten‘ Bereiches machen:

With Worksheets("TabelleXY")
 .Range("25:35").Value = .Range("20:30").Value
End With

…ist es unbedingt notwendig die ganzen Zeilen zu kopieren…?

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#3

Salu Thomas,

Wenn ich dich richtig verstehe (dazu äusserst Du dich nicht
wirklich, das lese ich aus dem Titel des Beitrages und dem
Code) willst Du nur die Werte in den neuen Bereich einfügen?

Ja, hier wollte ich nur die Werte kopieren, aber eben möglichst ohne Select oder Activate.

Wenn Du mit Copy/Paste arbeitest, dann geht da ausser
.PaseSpecial nicht viel anderes.

Dann ist das also einer der Fälle, wo man die Blätter activaten, und die Zielzellen selecten muß? Wenn ich nämlich auch nur eines davon weglasse, funzt es nicht mehr.

Aber Du könntest den Spiess ja umdrehen und die Werte des
neuen Bereiches gleich denen des ‚alten‘ Bereiches machen:

With Worksheets(„TabelleXY“)
.Range(„25:35“).Value = .Range(„20:30“).Value
End With

Das geht? Cool! Probiere ich nachher gleich aus.

…ist es unbedingt notwendig die ganzen Zeilen zu
kopieren…?

Nun, eigentlich sind es nur drei Spalten, doch sie enthalten Matrix-Formeln, auf deren Ergebnisse sich statische Formeln in den Spalten daneben beziehen. Wenn ich die nicht mit-copy-paste, erhalte ich eine Fehlermeldung. Daher hatte ich die ganzen Zeilen gewählt.

Hast Du auch noch eine Idee zum Kopieren ohne Formatierung, nur mit den Formeln?

Vielen dicken Dank für Deine stets hilfreiche und kompetente Unterstützung!

Beste Grüße
-Rob.

#4

Grüezi Rob

Wenn Du mit Copy/Paste arbeitest, dann geht da ausser
.PaseSpecial nicht viel anderes.

Dann ist das also einer der Fälle, wo man die Blätter
activaten, und die Zielzellen selecten muß? Wenn ich nämlich
auch nur eines davon weglasse, funzt es nicht mehr.

.PasteSpecial funktioniert IMO nur im aktiven Tabellenblatt - dabei wird ja automatisch Selektiert und das geht halt nunmal nur im aktiven Tabellenblatt

Aber Du könntest den Spiess ja umdrehen und die Werte des
neuen Bereiches gleich denen des ‚alten‘ Bereiches machen:

With Worksheets(„TabelleXY“)
.Range(„25:35“).Value = .Range(„20:30“).Value
End With

Das geht? Cool! Probiere ich nachher gleich aus.

Klar geht das - und diesmal auch über die Tabellenblatt-Grenzen hinweg in andere Blätter oder sogar in andere (geöffnete) Mappen, das ist alles eine Frage der korrekten Referenzierung.

…ist es unbedingt notwendig die ganzen Zeilen zu
kopieren…?

Nun, eigentlich sind es nur drei Spalten, doch sie enthalten
Matrix-Formeln, auf deren Ergebnisse sich statische Formeln in
den Spalten daneben beziehen. Wenn ich die nicht
mit-copy-paste, erhalte ich eine Fehlermeldung. Daher hatte
ich die ganzen Zeilen gewählt.

Dann versuche mal nur den entsprechenden Bereich mit der .Value-Eigenschaft zu übertragen.

Hast Du auch noch eine Idee zum Kopieren ohne Formatierung,
nur mit den Formeln?

Hmmm, genau gleich aber mit .Formula anstelle von .Value (dabei können aber relativ adressierte Bezüge sich verschieben).

Vielen dicken Dank für Deine stets hilfreiche und kompetente
Unterstützung!

Aber gerne doch - bei so nettem Feedback macht das Ganze dann gleich nochmal so viel Spass :smile:

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
1 Like
#5

Hallo Rob,

Wie kann ich das Paradigma „vermeide select und activate“ hier
umsetzen?

Sub tt()
With Tabelle1
 .Range("20:30").Copy
 .Range("25:35").Select
 .PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, IconFileName:=False
End With
With Tabelle1
 .Range("20:30").Copy
 .Range("25:35").PasteSpecial 'Format:=3, Link:=1, DisplayAsIcon:=False, IconFileName:=False
End With
End Sub

einige Bemerkungen zum Code. Ich finde Format:=3 sehr ungünstig.
Siehst du den Code nach paar Wochen wieder weißt du dann noch für was die 3 steht? Ich nehme es nicht an. Also nimm den sprechenden Namen. Bei Link:=1 das Gleiche.

Wie du siehst habe ich die Form Tabelle1 und nicht Worksheets(„tabelle1“) genommen. Das hat den Riesenvorteil die intelli sense wird aktiviert.
Schreib mal in eine Zeile in der With-Schleife einen Punkt, dann siehste das.
Genauso wenn du den Cursor direkt hinter.
.Range(„25:35“).PasteSpecial
stellst und ein Leerzeichen eintippst…

Sowohl in XL2000 als auch XL2007 existiert PasteSpezial mit zwei Syntaxen.
Einmal für ein Worksheet-Objekt (deine erste With-Schleife) dann noch
für ein Range-Objekt, (deine zweite With-Schleife)

Deshalb bedingt das zweite PasteSpecial andere Parameter und der Debugger beschwert sich über ihm unbekannte Argumente.wie „Format“
Achja, „funktioniert nicht“ ist für eine Anfrage viel zu Informationsarm.
Hilfreich ist bei Code grundsätzlich anzugeben in welcher Codezeile kommt
welcher Fehler.

Wie kann ich mit Formeln (!) Copy-Pasten ohne daß ich die

Formatierung mitkopiere?

Da bist du ja schon bei Thomas in excellenten Händen.

PS: Ich habe jetzt nur deinen Code zum laufen gebracht. Was genau da kopiert wurde habe ich noch gar nicht geprüft.

Gruß
Reinhard