Werte aus Sheet in andere Sheets per VBA kopieren

Moderator: ModerationP

Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Heimi65199 » 13. Jan 2018, 18:29

Hallo zusammen,
ich habe ein Problem, dass ich aus einem Sheet (in dem ich plane "Einteilungen 2017") die einzelnen Daten (Zahlen und Farben) gerne kopiert hätte in andere Sheets, damit der doppelte manuelle Aufwand entfällt.

Aktuell habe ich folgenden Code probiert:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Einteilung 2017").Range("j6:k60").Copy Destination:=Sheets("F1").Range("B2:C56")
Sheets("Einteilung 2017").Range("L6:M60").Copy Destination:=Sheets("F2").Range("B2:C56")
Sheets("Einteilung 2017").Range("n6:o60").Copy Destination:=Sheets("F3").Range("B2:C56")
Sheets("Einteilung 2017").Range("P6:Q60").Copy Destination:=Sheets("F4").Range("B2:C56")
Sheets("Einteilung 2017").Range("r6:s60").Copy Destination:=Sheets("F5").Range("B2:C56")
Sheets("Einteilung 2017").Range("t6:u60").Copy Destination:=Sheets("F6").Range("B2:C56")
Sheets("Einteilung 2017").Range("v6:w60").Copy Destination:=Sheets("F7").Range("B2:C56")
Sheets("Einteilung 2017").Range("x6:y60").Copy Destination:=Sheets("F8").Range("B2:C56")
Sheets("Einteilung 2017").Range("z6:aa60").Copy Destination:=Sheets("F9").Range("B2:C56")
Sheets("Einteilung 2017").Range("AB6:AC60").Copy Destination:=Sheets("F10").Range("B2:C56")
Sheets("Einteilung 2017").Range("AD6:AE60").Copy Destination:=Sheets("F11").Range("B2:C56")
Sheets("Einteilung 2017").Range("af6:ag60").Copy Destination:=Sheets("F12").Range("B2:C56")
Sheets("Einteilung 2017").Range("AH6:ai60").Copy Destination:=Sheets("F13").Range("B2:C56")
Sheets("Einteilung 2017").Range("aj6:ak60").Copy Destination:=Sheets("F14").Range("B2:C56")
Sheets("Einteilung 2017").Range("aL6:aM60").Copy Destination:=Sheets("F15").Range("B2:C56")
Sheets("Einteilung 2017").Range("an6:ao60").Copy Destination:=Sheets("F16").Range("B2:C56")
Sheets("Einteilung 2017").Range("ap6:aq60").Copy Destination:=Sheets("F17").Range("B2:C56")
Sheets("Einteilung 2017").Range("ar6:as60").Copy Destination:=Sheets("F18").Range("B2:C56")
Sheets("Einteilung 2017").Range("at6:au60").Copy Destination:=Sheets("F19").Range("B2:C56")
Sheets("Einteilung 2017").Range("av6:aw60").Copy Destination:=Sheets("F20").Range("B2:C56")
Sheets("Einteilung 2017").Range("ax6:ay60").Copy Destination:=Sheets("F21").Range("B2:C56")
End Sub

Also aus dem Sheet "Einteilungen 2017" sollen aus der Spalte j6:k60 und Daten kopiert werden in jeweils ein neues anderes Sheet. Die Quelle ändert sich und das Ziel bleibt bis auf die unterschiedlichen Sheet gleich!

Habe ich alle Zeilen aktiv im Code, so habe ich Probleme auf den Quellearbeitsblatt "Einteilung 2017".

Könnte man so etwas mit einer Schleife lösen?

Mir fehlt leider das nötige Wissen dazu!

Ich hoffe ihr könnt mir hier helfen !

Vielen Dank für die Mühe!

Gruß Sven
Heimi65199
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 22
Registriert: 11. Apr 2014, 21:38

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Flotter Feger » 13. Jan 2018, 18:39

Hallöchen Sven,
Habe ich alle Zeilen aktiv im Code, so habe ich Probleme auf den Quellearbeitsblatt "Einteilung 2017".

... und welche wären das genau ?
VG Sabina

bei mir läuft Win 7 32-Bit - Office 2010 Pro Plus 32-Bit und Office 2016 Pro Plus 32-Bit
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1167
Registriert: 24. Okt 2016, 16:40

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Heimi65199 » 13. Jan 2018, 18:50

Hallo Sabina,

ich habe dann das Problem, wenn alle Zeilen aktiv sind im Code, dass ich kurioserweise im Sheet "Einteilungen 2017" ständig die Spalten B2:C56 markiert bekomme und ich keine andere Zelle anklicken kann!
So wie in einer Endlosschleife!

Obwohl ich ja in den anderen Sheets die Daten auf die Spalten B2:C56 (immer die gleichen Spalten) kopiert haben möchte.

Mein Hintergrund ist, dass ich Änderungen mache in dem Sheet "Einteilungen 2017" (Zahlen und Farben) und diese aktuell in den unterschiedlichen Sheets kopiert werden sollen (F1, F2, F3 usw.).
Heimi65199
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 22
Registriert: 11. Apr 2014, 21:38

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Flotter Feger » 13. Jan 2018, 18:52

Hallöchen,

So sollte es klappen.
Code: Alles auswählen
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long, j As Long

Application.EnableEvents = False

j = 1
For i = 10 To 50 Step 2
    With Sheets('Einteilung 2017")
        .Range(.Cells(6, i), .Cells(60, i + 1)).Copy Destination:=Sheets("F" & CStr(j)).Range("B2")
        j = j + 1
    End With
Next i

Application.EnableEvents = True
End Sub
VG Sabina

bei mir läuft Win 7 32-Bit - Office 2010 Pro Plus 32-Bit und Office 2016 Pro Plus 32-Bit
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1167
Registriert: 24. Okt 2016, 16:40

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Heimi65199 » 13. Jan 2018, 19:40

Vielen Dank für diese schnelle Lösung!

Nur schein ich doch solch ein Laie zu sein, dass ich es entweder falsch umgesetzt oder an die falsche Stelle kopiert habe.

Leider bekomme ich keine Kopien auf die anderen Sheets!

Was habe ich falsch gemacht!?
Heimi65199
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 22
Registriert: 11. Apr 2014, 21:38

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Flotter Feger » 13. Jan 2018, 20:19

Hallöchen,

wann möchtest du denn genau haben, dass die Daten kopiert werden ?
Bei jedem Klick in eine Zelle des Worksheets("Einteilung 2017"), so wie du es gerade über das Worksheet_SelectionChange-Event steuerst, anscheinend dann doch nicht ?

Vermutlich nur, wenn sich in einer Zelle des Sheets etwas ändert ?
VG Sabina

bei mir läuft Win 7 32-Bit - Office 2010 Pro Plus 32-Bit und Office 2016 Pro Plus 32-Bit
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1167
Registriert: 24. Okt 2016, 16:40

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Heimi65199 » 13. Jan 2018, 20:51

Hallo Sabina,

ich habe gerade nochmals etwas rumprobiert.

Nun scheint es schon soweit zu klappen. Vielen vielen Dank dafür!

Die Übernahme der Farben und des Zelleninhalten sollte schon übernommen werden auf das jeweilige Sheet, wenn der Eintrag abgeschlossen ist. Das funktioniert soweit ich es getestet hatte immer dann, wenn ich die Zelle wechsel. Super soweit.
Also nur, wenn sich etwas ändert.

Derzeit bemerke ich eine Aktion/Berechnung von Excel sobald ich die Zelle wechsel. Das ist eigentlich gut so, da so es immer aktuell ist! Nimmt aber jedesmal Zeit in Anspruch!
Könnte man die Berechnung auch manuell auslösen lassen. Da jedes Mal etwas Zeit ins Land geht nach einer Änderung?

Ich habe auch wieder diese Nebenwirkung, dass des Öfteren die Spalten B&C markiert werden, wenn ich die Spalten AA und AB markieren möchte um diese wiederum zu löschen! Und löschen lassen sie sich nicht, weil sie auf Spalten B und C springen.
Heimi65199
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 22
Registriert: 11. Apr 2014, 21:38

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Flotter Feger » 13. Jan 2018, 22:48

Hallöchen,

probier es doch mal mit dem Change-Event anstatt SelectionChange ...
Code: Alles auswählen
Private Sub Worksheet_Change(ByVal Target As Range)

Das tritt nur auf, wenn du etwas in einer Zelle änderst und die Eingabe-Taste oder die TAB-Taste drückt ... oder die Pfeiltasten ...

Du kannst auch noch den Bereich einschränken, der auf die Änderung der Zellen reagieren soll und das Kopieren der Daten startet.
VG Sabina

bei mir läuft Win 7 32-Bit - Office 2010 Pro Plus 32-Bit und Office 2016 Pro Plus 32-Bit
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1167
Registriert: 24. Okt 2016, 16:40

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Heimi65199 » 13. Jan 2018, 23:07

Flotter Feger hat geschrieben:Hallöchen,

probier es doch mal mit dem Change-Event anstatt SelectionChange ...
Code: Alles auswählen
Private Sub Worksheet_Change(ByVal Target As Range)

Das tritt nur auf, wenn du etwas in einer Zelle änderst und die Eingabe-Taste oder die TAB-Taste drückt ... oder die Pfeiltasten ...

Du kannst auch noch den Bereich einschränken, der auf die Änderung der Zellen reagieren soll und das Kopieren der Daten startet.



Das hat nun super geklappt! Ich als Laie hätte das nie gefunden!
Ich hoffe es ist nun nicht unverschämt hier auch noch zu fragen aber ich möchte gerne die übertragenen Farben aus "Einteilung 2017" in die Sheets F1, F2, F3 usw. einen Text zuweisen in einer weiteren Spalte :roll: .

Jede Farbe bedeutet bei mir einen bestimmten Text. Also ganz simpel ausgedrückt: Wenn Farbe (rot, blau, gelb, grün usw.) dann "Text".

Ich habe nun einerseits gelesen es geht über VBA aber auch nicht sehr einfach und andererseits habe ich gelesen man sollen "Namen" definieren. Aber mit "Namen definieren" komme ich nicht weiter.
Heimi65199
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 22
Registriert: 11. Apr 2014, 21:38

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Flotter Feger » 13. Jan 2018, 23:33

Hallöchen,

Wie erstellst du die "Farben" ? Händisch oder über eine bedingte Formatierung ... vermutlich händisch ...
Wo sind welche Farben und welche Farbe soll welchen Text wohin schreiben ?

Am besten du erstellst mal eine Musterdatei und lädst sie hier hoch. In der Datei die Ausgangssituation und alle Wunschergebnisse ganz genau angeben.
Es müssen aber nicht alle Tabellen in der Datei sein, es reichen 1 oder 2, wenn bereits daraus die Logik zu ersehen ist.
Und wenn möglich eine kleine Tabelle erstellen, mit der Gegenüberstellung "Farbe --> Text" ... um das mal vor Augen zu haben.
Ich brauche immer ein wenig Visualisierung, um mir das vorstellen zu können. Ein typisch weibliches Problem, hab ich mir sagen lassen. :wink:
VG Sabina

bei mir läuft Win 7 32-Bit - Office 2010 Pro Plus 32-Bit und Office 2016 Pro Plus 32-Bit
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1167
Registriert: 24. Okt 2016, 16:40

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Heimi65199 » 14. Jan 2018, 11:23

Hallo Sabina,

alles kein Problem, da Du Dir so viel Mühe machst!

Die Farben trage ich manuell ein. Wähle also für eine bestimmte Aufgabe eine bestimmte Farbe in dem Sheet "Einteilungen 2018".
Die manuell eingefärbten Spalten möchte ich dann auf einem anderen Sheet als "Detailansicht" sehen.

Da es aber einige Farben sind, möchte ich jeweils in einer weiteren rechten Spalte den Namen dazu schreiben. Kann das mir nicht mehr alles merken.

Um nicht jedes mal alles per Hand zu kopieren wollte ich diese Liste automatisieren.

Soll heißen: 1x alles in dem Sheet "Einteilungen 2018" eintragen und doppelte Arbeit (auf den Sheets F1, F2, F3 ... F21) vermeiden.

Kann ich Dir die Datei auch per PN schicken ?
Heimi65199
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 22
Registriert: 11. Apr 2014, 21:38

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Flotter Feger » 14. Jan 2018, 12:58

Hallöchen,

an eine PN können keine Dateien angehängt werden ... lade doch hier im Thread eine anonymisierte Datei hoch.

Eigentlich ist es ja gar nicht so gut, aus Excel einen Tuschkasten zu machen ...

Andere Idee ... wie wäre es mit Kommentaren in den Zellen anstatt dem Text ? Der Kommentar wird mitgenommen beim Kopieren.
Du könntest dir eine kleine DialogBox (UserForm) bauen, die gleichzeitig die Hintergrundfarbe der Zelle setzt und einen Kommentar in die Zelle schreibt.
VG Sabina

bei mir läuft Win 7 32-Bit - Office 2010 Pro Plus 32-Bit und Office 2016 Pro Plus 32-Bit
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1167
Registriert: 24. Okt 2016, 16:40

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Heimi65199 » 14. Jan 2018, 13:06

Flotter Feger hat geschrieben:Hallöchen,

an eine PN können keine Dateien angehängt werden ... lade doch hier im Thread eine anonymisierte Datei hoch.

Eigentlich ist es ja gar nicht so gut, aus Excel einen Tuschkasten zu machen ...

Andere Idee ... wie wäre es mit Kommentaren in den Zellen anstatt dem Text ? Der Kommentar wird mitgenommen beim Kopieren.
Du könntest dir eine kleine DialogBox (UserForm) bauen, die gleichzeitig die Hintergrundfarbe der Zelle setzt und einen Kommentar in die Zelle schreibt.



O.k., ich habe dann doch lieber mal die Datei anonymisiert, da es für mich einfacher ist als dieses Userform zu machen. Alles noch ein bisschen viel Neuland!
In der Anlage die Datei!

Mal sehen ob ich es als Beispiel wenigsten schön gemacht habe! ;-)
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Heimi65199
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 22
Registriert: 11. Apr 2014, 21:38

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon juvee » 14. Jan 2018, 13:43

Hi,

Code: Alles auswählen
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long,

If Not Intersect(Target, Range("J6:AY60")) Is Nothing And Target.Count = 1 Then
 If Target.Column Mod 2 = 0 Then
    i = Cells(2, Target.Column)
 Else
    i = Cells(2, Target.Column - 1)
 End If
 
 Cells(6, i).Resize(60, 2).Copy Destination:=Sheets("F" & i).Range("B2")
End If

End Sub



Dieses Makro reagiert lediglich für die betroffene Detailtabelle, indem es auswertet, in welcher Spalte der Eingabetabelle im Bereich J6:AY60 etwas verändert wurde und kopiert die SA/SO Spalte in die entsprechende Tabelle B2.

VG Juvee
juvee
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1634
Registriert: 05. Jun 2014, 12:48

Re: Werte aus Sheet in andere Sheets per VBA kopieren

Beitragvon Heimi65199 » 14. Jan 2018, 15:32

juvee hat geschrieben:Hi,

Code: Alles auswählen
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long,

If Not Intersect(Target, Range("J6:AY60")) Is Nothing And Target.Count = 1 Then
 If Target.Column Mod 2 = 0 Then
    i = Cells(2, Target.Column)
 Else
    i = Cells(2, Target.Column - 1)
 End If
 
 Cells(6, i).Resize(60, 2).Copy Destination:=Sheets("F" & i).Range("B2")
End If

End Sub



Dieses Makro reagiert lediglich für die betroffene Detailtabelle, indem es auswertet, in welcher Spalte der Eingabetabelle im Bereich J6:AY60 etwas verändert wurde und kopiert die SA/SO Spalte in die entsprechende Tabelle B2.

VG Juvee


Hallo Juvee,

Deinen Code habe ich auch nun mal probiert. Wann aktualisiert er mir die einzelnen "F" Sheets ? Weder Farbe noch der Eintrag haben sich nach Änderung in der ursprünglichen "Einteilung 2018" Sheet geändert in den einzelnen "F"-Sheets.

Möchte ich Speichern, sagt Excel, dass er neu berechnen müsse. Das gefühlte unendliche male!

Gruß Sven
Heimi65199
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 22
Registriert: 11. Apr 2014, 21:38

Nächste

Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: natitris und 12 Gäste