Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1400to1404
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
Inhaltsverzeichnis

Werte einer Spalte kopieren

Werte einer Spalte kopieren
06.01.2015 21:47:31
Barbara

Hallo
Habe eine schreibgeschützte Excel-Tabelle, in der ich folgendes per Makro tun möchte:
Die Werte der Spalte G in eine neue Spalte H kopieren, wobei alle Spalten rechts davon um eine Spalte weiter rücken.
Dabei sollen die Werte und die Formatierung kopiert werden, also nicht die Formeln.
Die nächste Schwierigkeit:
Weiters sollen alle kopierten Zellen eine bedingte Formatierung bekommen:
Wenn (bei späterer Bearbeitung) eine Zelle von Spalte G und Spalte H nicht gleich sind, soll die Zelle in Spalte H einen rosa Hintergrund bekommen.
Dann soll die oberste Zelle H1 das aktuelle Datum und Uhrzeit der Erstellung bekommen.
Der Schreibschutz soll dann wieder aktiviert sein.
Habe es mit dem Makrorekorder probiert, war aber nicht sehr erfolgreich damit.
Ist das per VBA möglich?
LG,
Barbara

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

Betreff
Datum
Anwender
Anzeige
AW: Werte einer Spalte kopieren
06.01.2015 22:02:42
Daniel
Hi
alles, was man von Hand machen kann, geht auch mit einem Makro.
Bei Verwendung des Recorders muss man beachten, dass dieser keinen "fertigen" Code prodziert, sonderen das aufgezeichnete Ergebnis noch überarbeitet und gekürzt werden muss.
(im Prinzip ist der Recorder für das Arbeiten mit VBA so nützlich wie ein Wörterbuch beim Übersetzen aus einer Fremdsprache)
probiers mal so:
Sub test()
ActiveSheet.Unprotect Password:="xxx"
Columns(8).Insert
Columns(7).Copy
Columns(8).PasteSpecial xlPasteValues
Columns(8).PasteSpecial xlPasteFormats
Cells(1, 8).Value = Now
Cells(1, 8).Activate
With Columns(8)
.FormatConditions.Add Type:=xlExpression, Formula1:="=H1<>G1"
.FormatConditions(.FormatConditions.Count).Interior.Color = 16764159
End With
ActiveSheet.Protect Password:="xxx"
End Sub
Gruß Daniel

Anzeige
AW: Werte einer Spalte kopieren
06.01.2015 23:18:22
Barbara
Hallo Daniel,
die Tinte ist noch nicht trocken, schon kommt die Antwort. Das war ja super schnell und Dein Beispiel funktioniert bestens. Vielen Dank, Daniel.
Nun noch einige Zusätze bitte:
Die zuvor aktive Zelle soll danach wieder aktiv sein. habs mit set a = activecell probiert, aber das war nur ein blinder Versuch einer Einäugigen, oder umgekehrt.
Da gibt es noch ein Makro, das bei jeder Eingabe anspringt:
Private Sub Worksheet_Change(ByVal Target As Range)
Das stört zwar nicht, aber der guten Ordnung halber: Kann man das für dieses Makro ausschalten?
LG,
Barbara

Anzeige
AW: Werte einer Spalte kopieren
06.01.2015 23:31:46
Daniel
HI
ohne Änderung der aktiven Zelle, läuft aber so nur in der deutschen Sprachversion:
Sub test()
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="xxx"
Columns(8).Insert
Columns(7).Copy
Columns(8).PasteSpecial xlPasteValues
Columns(8).PasteSpecial xlPasteFormats
Cells(1, 8).Value = Now
With Columns(8)
.FormatConditions.Add Type:=xlExpression, Formula1:="=Z(0)S(0)<>Z(0)S(-1)"
.FormatConditions(.FormatConditions.Count).Interior.Color = 16764159
End With
ActiveSheet.Protect Password:="xxx"
Application.EnableEvents = True
End Sub
Gruß Daniel

Anzeige
AW: Werte einer Spalte kopieren
07.01.2015 00:03:59
Barbara
Mit Application.EnableEvents geht es.
Aber die Spalte ist danach dennoch markiert und nicht die zuvor aktive Zelle.
Geht das nicht mit activecell oder so ähnlich?
LG, Barbara

schreib die Zelladresse in eine Variable ...
07.01.2015 00:15:07
Matthias L
Hallo Barbara
Ich weiß nicht ob Daniel noch im Forum unterwegs ist, deshalb mein Vorschlag:
Speichere die Zelladresse als String in einer Variable
Option Explicit
Sub test()
Dim StartZelle$
StartZelle = ActiveCell.Address
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="xxx"
Columns(8).Insert
Columns(7).Copy
Columns(8).PasteSpecial xlPasteValues
Columns(8).PasteSpecial xlPasteFormats
Cells(1, 8).Value = Now
With Columns(8)
.FormatConditions.Add Type:=xlExpression, Formula1:="=Z(0)S(0)<>Z(0)S(-1)"
.FormatConditions(.FormatConditions.Count).Interior.Color = 16764159
End With
ActiveSheet.Protect Password:="xxx"
Range(StartZelle).Activate
Application.EnableEvents = True
End Sub
evtl. noch ein ScreenUpdating = False mit einpflegen, dann flackerts auch nicht.
Gruß Matthias

Anzeige
Korrektur
07.01.2015 00:36:39
Matthias L
Hallo
Kleine Korrektur
Hier ist mal .Select ganz hilfreich, also nicht .Activate
Dim StartZelle$
StartZelle = ActiveCell.Address
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
ActiveSheet.Unprotect Password:="xxx"
Columns(8).Insert
Columns(7).Copy
Columns(8).PasteSpecial xlPasteValues
Columns(8).PasteSpecial xlPasteFormats
Cells(1, 8).Value = Now
With Columns(8)
.FormatConditions.Add Type:=xlExpression, Formula1:="=Z(0)S(0)<>Z(0)S(-1)"
.FormatConditions(.FormatConditions.Count).Interior.Color = 16764159
End With
ActiveSheet.Protect Password:="xxx"
Range(StartZelle).Select
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Gruß Matthias

Anzeige
Das sollte ebenso wie Mattis Erweiterung von ...
07.01.2015 01:41:33
Luc:-?
…Daniels Code, die ich soeben erst gesehen habe, Barbara,
alle deine (Zusatz-)Wünsche erfüllen und ist außerdem für jede halbwegs aktuelle lokale XlVersion (inkl US-Original) geeignet und darüberhinaus leicht aktualisierbar:
Sub ColumnAndUniFormFConAdd()
Const naFml$ = "xlFCOrF", txFml$ = "=RC<>RC[-1]", txPw$ = "xyz"
Dim LokFml$, aktZ As Range
Application.EnableEvents = False
ActiveSheet.Unprotect txPw: Set aktZ = ActiveCell
Me.Columns(8).Insert
With Me.Names
.Add naFml, "=0": .Item(naFml).RefersToR1C1 = txFml
LokFml = Replace(.Item(naFml).RefersToR1C1Local, Me.Name & "!", "")
.Item(naFml).Delete
End With
Me.Columns(7).Copy
With Me.Columns(8)
.PasteSpecial xlPasteValues: .PasteSpecial xlPasteFormats
Me.Cells(1, 8) = Now
With .FormatConditions
.Add xlExpression, , LokFml: .Item(.Count).Interior.Color = &HFFCCFF
End With
End With
aktZ.Select: Me.Protect txPw: Set aktZ = Nothing
Application.EnableEvents = True
End Sub
Achtung! Der PgmCode gehört in das DokumentKlassenModul des betroffenen Blattes!
Morrn (mit spez Gruß an Matti), Luc :-?

Anzeige
AW: Das sollte ebenso wie Mattis Erweiterung von ...
07.01.2015 20:20:34
Barbara
Sieht gut aus, auch wenn ich so manches nicht verstehe.
Aber die aktive Zelle bleibt, wo sie ist.
Danke Luc.

Das macht sie auch bei Mattis Variante, ...
07.01.2015 21:15:19
Luc:-?
…Barbara; ;-)
das Besondere bei meinem Code ist das, was ich dazu geschrieben hatte. Damit das Pgm auch auf PCs mit unterschiedlichen lokalen (nationalen) Einstellungen laufen kann, habe ich die BedingtFormatFml in US-Notation als Konstante vorgegeben. Diese wird in einen temporär angelegten Namen als Original-US-Fml übertragen und als lokale Fml wieder ausgelesen, d.h., Xl wird benutzt, um die Fml in die jeweils benötigte lokale Form zu übersetzen, denn nur die darf als BedingtFormatFml* eingetragen wdn.
* Kann aber dort auch in US-Notation ausgelesen wdn (auch ohne den Umweg über defNamen, wenn man weiß wie ;-]).
Viel Erfolg! Gruß, Luc :-?

Anzeige
mir ist da was aufgefallen Luc ...
08.01.2015 01:06:47
Matthias L
Hallo
Wenn eine Zelle die bereits einen Datum bekommen hat die aktive Zelle ist und der Code wird erneut gestartet dann wird die ActiveCell um Offset(,1) verschoben.
Aber nur wenn eine solche Zelle aktiviert ist.
Auch Gerds Anmerkung habe ich mal getestet.
Man kann den Code ja auch aus Tabelle1 starten obwohl er z.B. in Tabelle2 steht.
Jedenfalls ein DAU ;o) Dann gibts nen VBA-Crash
Kann das immer nur bis XL2007 testen.
Habe ich das alles richtig erkannt,
oder liegts an meiner XL-Version?
Gruß Matthias

Ich hatte geschrieben (in Rot!), dass die ...
08.01.2015 04:26:32
Luc:-?
…Prozedur ins DokKlassenModul des betroffenen Blattes gehört, Matti,
weshalb ich davon ausgehe, dass das auch das Blatt ist, in dem gearbeitet wird und deshalb aktiv ist. Das Pgm kann zwar auch von einem anderen Blatt aus gestartet wdn, aber das hat ja keinen Sinn. Muss man mit solchen DAUs rechnen, kann das pgmtechnisch verhindert wdn (hier reicht ggf schon der Vorsatz Private und Aufruf über einen Button auf dem betroffenen Blatt (zB Tabelle1.makroname).
Des weiteren gehe ich davon aus, dass mit der zuerst erfolgenden EinfügeOperation der richtige Bereich in Gänze ausgewählt ist, so dass das temporäre Anlegen des Namens für die FmlÜbersetzung kein Problem ist. Es sollte nur nicht die 1.Spalte sein, aber das ist durch den Code ja ausgeschlossen.
Übrigens habe ich die einsatz­zweck­bedingten Spezifika nicht getestet, sondern nur tempNamens- und BedFmt­Bildung. Zu PgmWiederholung und ggf dadurch bewirkter Verschiebung der aktZelle kann ich deshalb nichts sagen. Bei der Fixierung des Pgms auf eine bestimmte Spalte habe ich mich hierbei auf die Vorgabe verlassen. Falls das also erforderlich sein sollte, muss Barbara das noch berücksichtigen. Dabei will ich mal annehmen, dass dieser Fehler in deiner PgmVariante bei gleicher Vorgabe nicht auftritt! Oder…?
Ich habe die gleiche xlVersion wie du verwendet.
Morrn, Luc :-?

Anzeige
AW: Ich hatte geschrieben (in Rot!), dass die ...
08.01.2015 08:17:19
Matthias L
Hallo Luc
Ich hatte geschrieben (in Rot!), dass die ...
Das weiß ich doch Luc. Ich bin ja kein DAU ;-)
Ich hoffe nicht das Du meine Nachfrage als Kritik aufgefasst hast.
Kann ich mir eigentlich nicht vorstellen.
Du weißt ja das ich Deine Beiträge immer mit sehr viel Interesse lese und fast alles teste.
Das hilft mir ja um meinen "Horizont" zu erweitern :-)
Gruß Matthias

Nee, natürlich nicht, Matti, aber ...
08.01.2015 15:32:52
Luc:-?
…Gerd hat den roten Text wohl übersehen… ;-]
Den von mir vorgeschlagenen „DAU-Schutz“ habe ich übrigens erfolgreich getestet. Der dürfte nun, falls überhaupt, nur absichtlich aushebelbar sein.
Für alles andere ist ZweckGerechtigkeit und Handhabung ausschlaggebend. Das muss dann Barbara wissen/tun.
Gruß, Luc :-?

Anzeige
AW: ActiveSheet / Me ?
07.01.2015 22:32:31
Gerd L
Hi Luc,
weshalb hast du auf die Blattaktivierung im Code verzichtet?
Gruß Gerd

Siehe AW an Matti, d.Blatt muss aktiv sein! orT
08.01.2015 04:28:43
Luc:-?
Morrn, Luc :-?

329 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige