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

Excel Tabelle kopieren

Excel Tabelle kopieren
19.01.2015 10:40:08
René
Hallo,
ich habe folgendes Problem.
Ich schreibe derzeit an einer ExcelDatei für Second Hand Shops / Speziell Kleidung.
Ich möchte wenn man auf einen Button klickt eine komplette Tabelle kopiert mit neuen Namen und es sollen auch einige Werte ausgelesen und dann eingetragen werden.
Kann mir wer da helfen? Ich kann mit Excel gut umgehen aber die VBA Befehle bzw. Bezeichnungen kenne ich nicht :(
Würde mich sehr freuen

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

Betreff
Datum
Anwender
Anzeige
AW: Excel Tabelle kopieren
19.01.2015 10:50:25
Klaus
Hallo Rene,
ich kenne ja deine Datei nicht. Im Prinzip geht das so:
Sub MakroAufButton()
'aus Tabelle1 (Mustertabelle) eine neue, leere Tabelle erstellen
Sheets.Add                              'neues Blatt
With ActiveSheet                        'die neue Tabelle ist immer im Focus, also active!
Sheets("Tabelle1").Cells.Copy       'aus Muster kopieren
.Range("A1").PasteSpecial           'in neue Tabelle hineinkopieren
.Range("C9:C12").ClearContents      'bestimmte Inhalte löschen
.Range("A4").Value = Date           'bestimmte Inhalte einfügen
.Range("G7").Value = "KUNDENNAME!"  'bestimmte Inhalte einfügen
.Name = "Neues Blatt_" & Format(Date, "yymmdd") & "_" & Format(Time, "hhmm")
End With
End Sub
Die Namen, Bereiche und Werte kannst du bestimmt selbst an deine eigene Datei anpassen.
Grüße,
Klaus M.vdT.

Anzeige
AW: Excel Tabelle kopieren
19.01.2015 11:15:55
René
Hallo,
ich hab die Datei mal als Anhang angefügt. Also ich möchte das die tabelle Abgabe komplett kopiert wird (auch mit bildern) aber die Zeilen E18:E46 mit den Inhalt aus Tabelle Auszahlung G18:G46 gefüllt wird. Und dann soll gleichzeitig die tabelle Auszahlung mit den Formeln (natürlich evtl gleich angepasst an die Kopie) erstellt werden. Gäbe es da eine möglichkeit?
https://www.herber.de/bbs/user/95095.xlsx

AW: Excel Tabelle kopieren
19.01.2015 12:41:51
Klaus
Hallo Rene,
hier das Makro für deinen Button. Denk daran, du musst als *.xlsm speichern wenn du Makros benutzt. Ich habe den Code ausführlich kommentiert.
Sub NeueAufgabeErstellen()
'** aus (Mustertabelle) eine neue, leere Tabelle erstellen und füllen
'** Neues Blatt ganz am Anfang erstellen
Sheets("Abgabe").Copy Before:=Sheets(1)
With ActiveSheet  '** die neue Tabelle ist immer im Focus, also active!
'** Name MUSS eindeutig sein! Dazu füge ich das Datum und die Zeit an den Namen an.
.Name = "Abgabe_" & Format(Date, "yymmdd") & "_" & Format(Time, "hhmmss")
'** E18:E46 löschen
.Range("E18:E46").ClearContents
'** Bereich aus "Auszahlung" holen
.Range("E18:E46").Value = Sheets("Auszahlung").Range("G18:G46").Value
End With
End Sub
Grüße,
Klaus M.vdT.

Anzeige
AW: Excel Tabelle kopieren
19.01.2015 13:28:58
René
Super funktioniert 1a :D
Dazu noch eine frage.. werden beim kopieren auch die Formeln mitkopiert? Und kann man diese evtl speziell anpassen. Weil die Beziehen sich ja teilweise auf die Tabelle...

AW: Excel Tabelle kopieren
19.01.2015 13:42:34
Klaus
Hallo Rene,
probiere das doch einfach aus?
In deiner Mustertabelle steht in Abgabe!H18 die Formel
=WENN(A18="";"";E18*WENN(F18 kl 50;F18*0,5;F18*0,6))
Lasse ich das Makro durchlaufen, steht im neu erstellten Blatt "Abgabe_150119_133209" in H18 die gleiche Formel. Alle Bezüge dich sich auf das Blatt beziehen, beziehen sich weiterhin auf das Blatt.
Willst du das nicht, musst du mit festen Bezügen arbeiten, dazu gibt es einen Trick. Schreibst du im Blatt "Abgabe" die Formel folgendermaßen:
=WENN(INDIREKT("Abgabe!A"&ZEILE(A18))="";"";E18*WENN(F18 kl 50;F18*0,5;F18*0,6))
dann bleibt der Bezug auf das Blatt "Abgabe" für das WENN erhalten. Die anderen Bezüge kannst du ebenso anpassen. Der Trick an der Sache ist, dass der Teil in Anführungsstrichen von Excel nur als Text erkannt wird und daher nicht verändert wird. Durch den "ZEILE()"-Hochzähler bleibt die Formel trotzdem dynamisch.
Das hier:
'** Bereich aus "Auszahlung" holen
.Range("E18:E46").Value = Sheets("Auszahlung").Range("G18:G46").Value
setzt absolute Werte, keine Formeln. Aus dem Kontext deiner Musterdatei habe ich interpretiert, dass das so gewünscht ist.
Du könntest die Formeln natürlich auch per VBA schreiben. Dazu bemühst du dann am besten den Makrorekorder.
Grüße,
Klaus M.vdT.
P.S.: das kleinerals-Zeichen habe ich aus der Formel rausgenommen, um die HTML Formatierung nicht zu zerschießen.

Anzeige
AW: Excel Tabelle kopieren
19.01.2015 14:10:22
René
Okay 1a Antwort :D bin richtig begeistert :D
hab jetzt auch ne 2 tabelle mit eingefügt die erstellt werden muss.
jetzt meine Frage.. wenn ich ne Abfrage mache als IF .... THEN ... ELSE ....
Wie muss ich das schreiben, zum beispiel soll geprüft werden ob in der Tabelle bei der Spalte Rest 0 ist das dann bei der Kopie die komplette zeile gelöscht wird.

AW: Excel Tabelle kopieren
19.01.2015 14:25:16
Klaus
Das geht im Prinzip so:
Sub LoescheWennNull()
Dim r As Range
For Each r In Range("G18:G46")
If r.Value = 0 Then
Range("A" & r.Row & ":H" & r.Row).ClearContents
End If
Next r
End Sub

direkt in deine Tabelle einbauen werde ich es nicht, weil mich deine Salami-Taktik ziemlich nervt. Warum hast du nicht gleich im ersten Beitrag die komplette Aufgabe gestellt? Sobald du den Code am laufen hast kommt dann die nächste Kleinigkeit - zum Beispiel, wie die Liste wieder durchgehend ohne Leerzeilen (die sind ja grad gelöscht worden) dargestellt werden kann?
Hättest du die gesamte Aufgabe am Anfang ausformuliert, hättest du jetzt schon die gesamte Lösung.
Aber ich tue es dir nach und werde auf eine freundliche Anrede und einen abschließenden Gruß nach meinen Beiträgen jetzt ebenfalls verzichten.

Anzeige
AW: Excel Tabelle kopieren
21.01.2015 13:31:13
René
Hallo Klaus,
erstmal großes Entschuldigung für meine Unhöfliche Art hier zu schreiben. War nicht mit Absicht.
Und Danke das du mir schon sehr geholfen hast. Natürlich hätte ich gleich am Anfang schreiben können was ich wollte. Theoretisch habe ich das auch gemacht, doch soweit hatte ich ehrlich nicht gedacht und das tut mir leid.
Würdest du mir / ihr nochmals helfen? Jetzt aber mit komplette Fragestellung :D
Also Problem war, es sollten 2 Tabellenblätter kopiert werden mit anderen Namen und kompletten Inhalt. Dann sollte bei Abgabe die zahlen geändert werden was auch super geklappt hat und auch funktioniert. Und ja du hattest recht die liste sollte ohne Leerzeilen sein.
Und eigentlich sollten die Zellen die formeln so angepasst bekommen das die auch wieder von den 2 neuen blätter abhängig sind und nicht von den vorherigen.
LG René Ebert

Anzeige
AW: Excel Tabelle kopieren
21.01.2015 14:25:49
Klaus
Hallo Rene,
bitte lad die Tabelle nochmal hoch (mit allen bisher erarbeiteten Makros), ich hab die nicht mehr. Den Teil mit dem Bezug der Formeln hab ich nicht verstanden, den musst du nochmal erklären. Soweit ich mich erinnere hatte deine Mustertabelle nur Formeln, die sich auf das eigene Blatt beziehen - und das bleibt ja auch so, wenn kopiert wird.
Wenn es Formel gibt die sich auf ein anderes Blatt beziehen und sich das ändern soll, brauche ich dafür ein Beispiel in deiner Tabelle.
Da du Fußzeilen und einen Druckbereich hast nehme ich an, das ganze soll ohne löschen von ganzen Zeilen geschehen - da das ja alle Bezüge zerschießen würd. Richtig?
Grüße,
Klaus M.vdT.

Anzeige
AW: Excel Tabelle kopieren
21.01.2015 17:41:11
René
Hallo Klaus,
vielen Dank für deine Schnelle Antwort.
Die Bezüge der Formeln auf dem Tabellenblatt Auszahlung bezieht sich auf das Tabellenblatt Abgabe siehe Auszahlung H18 zb. deshalb möchte ich eigentlich das die Formel sich dann auf das neue Tabellenblatt bezieht und nicht auf das "alte".
Wollte das mit einer Variable versuchen aber irgendwie fehlt mir da der ansatz :D.
Und du hast Recht, Druckbereich sowie Fußzeile sollte so bleiben und sich nicht verschieben.
Datei : https://www.herber.de/bbs/user/95145.xlsm
Nochmals Vielen dank das du mir dabei hilfst.
LG René

Anzeige
AW: Excel Tabelle kopieren
22.01.2015 10:15:51
Klaus
Hi Rene,
das mir den Formel begreife ich immer noch nicht, weil ich nicht dahinter steige wo welche Abgabe wie gerechnet werden soll. Mit
.Range("E18").FormulaLocal = "='Abgabe_" & tbzusatz & "'!E18"
Bist du aber schon auf dem richtigen Weg! Du kannst eine so konstruierte Formel auch dem ganzen Bereich zuweisen, wenn du die Formel im R1C1 Format schreibst (dazu einfach Makrorekorder an, Zelle anwählen, Enter, Makrorekorder aus - dann hast du die richtige schreibe)
.Range("E18:E46").FormulaR1C1 = "=Abgabe_" & tbzusatz & "!RC"
Allerdings: du löscht ja Zellen von dem einen Blatt, nicht dem anderen (die mit 0) - dann stimmen die Bezüge eh nicht mehr, oder du lässt die leeren Zeilen stehen. Da musst du dir eine andere Formel überlegen. Ich würde dir gerne helfen, aber in deiner Beispieldatei wird nur der letzte Eintrag als 0 gelöscht, das sortieren greift also nicht. Wenn du eine Mustertabelle zur Verfügung stellen kannst in der meinetwegen 10 Einträge sind, und Eintrag 3 und 7 werden auf 0 gesetzt (damit ich rumprobieren kann) dann helfe ich dir auch mit der neuen Formel.
Bis dahin hier der aktuelle Code ohne Sortiervorgang:

Sub NeueAufgabeErstellen()
'** Datum und Uhrzeit in einer Variable als String speichern
Dim tbzusatz As String
tbzusatz = Format(Date, "dd.mm.yy") & "_" & Format(Time, "hhmmss")
'** Tabellenblatt Abgabe Kopieren
Sheets("Abgabe").Copy Before:=Sheets(1)
With ActiveSheet
'** Tabellennamen vergeben
.Name = "Abgabe_" & tbzusatz
'** Zellen E18:E46 löschen
.Range("E18:E46").ClearContents
'** Zelle H12 Inhalt löschen (Lieferscheindatum)
.Range("H12").ClearContents
'** Bereich aus "Auszahlung" holen
.Range("E18:E46").Value = Sheets("Auszahlung").Range("G18:G46").Value
'******* Spalte E nach 0-Werten durchsuchen und diese löschen, danach sortieren
Dim r As Range
'0-Zeilen löschen
For Each r In .Range("E18:E46")
If r.Value = 0 Then
.Range("A" & r.Row & ":H" & r.Row).ClearContents
End If
Next r
' ################## sortiervorgang auskommentiert ###############################
'        'Zellenverbund aufheben - denn mit dem kann nicht sortiert werden!
'        .Range("A18:C46").UnMerge
'        'nach Spalte A sortieren - damit die Leerzeilen mittendrin raus fliegen
'        .Sort.SortFields.Clear
'        .Sort.SortFields.Add Key:=.Range("A18:A46"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
'        .Sort.SetRange .Range("A18:H46")
'        .Sort.Header = xlGuess
'        .Sort.MatchCase = False
'        .Sort.Orientation = xlTopToBottom
'        .Sort.SortMethod = xlPinYin
'        .Sort.Apply
End With
'** Tabellenblatt Abgabe Kopieren
Sheets("Auszahlung").Copy Before:=Sheets(2)
With ActiveSheet
'** Tabellennamen vergeben
.Name = "Auszahlung_" & tbzusatz
'** Zellen E18:E46 löschen
.Range("E18:E46").ClearContents
'** Zellen F18:F46 löschen
.Range("F18:F46").ClearContents
'** Zelle H11 Inhalt löschen (Auszahlungsdatum)
.Range("H11").ClearContents
'** Zellen E18:E46 Füllen aus der Tabellenblatt Auszahlung
'.Range("E18").FormulaLocal = "='Abgabe_" & tbzusatz & "'!E18"
.Range("E18:E46").FormulaR1C1 = "=Abgabe_" & tbzusatz & "!RC"
'** .Range("E18:E46").Value = Sheets("Auszahlung").Range("G18:G46").Value
End With
End Sub
Übrigens, sehr schöne Codeanpassung die du da mit meinen Vorschlägen gebaut hast. Weiter so!
Grüße,
Klaus M.vdT.

Anzeige
AW: Excel Tabelle kopieren
22.01.2015 12:06:50
René
Hallo lieber Klaus,
danke für deine Antwort und ebenfalls für deine Code Korrektur.
Ich lade nochmal die Datei hoch mit ein paar Beispielen darin.
https://www.herber.de/bbs/user/95165.xlsm
Ich habe wie du sehen wirst habe ich versucht die neuen tabellennamen in jeweils in 2 variablen zu speichern um damit evtl. die Formeln zu schreiben.
Aber mit meinem Versuch kommt immer ein Fehler und er schreibt die Formel nicht so rein wie ich gewollt hätte. Aber durch deine Lösung mit formularR1C1 sehe ich ja das er sich, auch mit meiner Variable, arbeiten kann und auch die Formel reinschreibt zb. hole die daten aus dem neuen Datenblatt. Aber wenn ich eine formel reinschreibe mit WENN nichts in Zelle Abgabe_neublatt!A18 drin steht dann schreib auch nix ansonsten hole den Inhalt aus Abgabe_neublatt!A18.
Vielen Dank für deine Mühe und hilfe
LG René

Anzeige
AW: Excel Tabelle kopieren
22.01.2015 12:14:15
René
Hallo lieber Klaus,
danke für deine Antwort und ebenfalls für deine Code Korrektur.
Ich lade nochmal die Datei hoch mit ein paar Beispielen darin.
https://www.herber.de/bbs/user/95165.xlsm
Ich habe wie du sehen wirst habe ich versucht die neuen tabellennamen in jeweils in 2 variablen zu speichern um damit evtl. die Formeln zu schreiben.
Aber mit meinem Versuch kommt immer ein Fehler und er schreibt die Formel nicht so rein wie ich gewollt hätte. Aber durch deine Lösung mit formularR1C1 sehe ich ja das er sich, auch mit meiner Variable, arbeiten kann und auch die Formel reinschreibt zb. hole die daten aus dem neuen Datenblatt. Aber wenn ich eine formel reinschreibe mit WENN nichts in Zelle Abgabe_neublatt!A18 drin steht dann schreib auch nix ansonsten hole den Inhalt aus Abgabe_neublatt!A18.
Vielen Dank für deine Mühe und hilfe
LG René

AW: Excel Tabelle kopieren
22.01.2015 13:58:20
Klaus
Hallo Rene,
ich habe mich bemüht, möglichst "deine" Programmierung nicht anzufassen. Würde ich das ganze Projekt jetzt von Grund auf neu schreiben, könnte ich bestimmt die Hälfte des Codes sparen :-) Aber besser ein langer Code den du verstehst als ein kurzer Code den du nicht verstehst, oder?
Ich habe mir herausgenommen, die Formeln im Code durch Werte zu ersetzen - sonst klappt das sortieren nicht. Meine Annahme: Das sind eh Ausdrucke, die nur einmalig gebraucht werden und nicht ständig aktualisieren sollen.
Weitere Kommentare im Code, frohes testen!
Grüße,
Klaus M.vdT.
Sub NeueAufgabeErstellen()
'** Datum und Uhrzeit in einer Variable als String speichern
Dim tbzusatz As String
tbzusatz = Format(Date, "dd.mm.yy") & "_" & Format(Time, "hhmmss")
'** Namen erstellen aus Abgabe und tbzusatz um formelerstellung und übergabe zu erleichtern
Dim tbab As String
tbab = "Abgabe_" & tbzusatz
'** Namen erstellen aus Auszahlung und tbzusatz um formelerstellung und übergabe zu erleichtern
Dim tbaus As String
tbaus = "Auszahlung_" & tbzusatz
'** Tabellenblatt Abgabe Kopieren
Sheets("Abgabe").Copy Before:=Sheets(1)
With ActiveSheet
'** Tabellennamen vergeben
.Name = tbab
'** Zellen E18:E46 löschen
.Range("E18:E46").ClearContents
'** Zelle H12 Inhalt löschen (Lieferscheindatum)
.Range("H12").ClearContents
'** Bereich aus "Auszahlung" holen
.Range("E18:E46").Value = Sheets("Auszahlung").Range("G18:G46").Value
'******* Spalte E nach 0-Werten durchsuchen und diese löschen
Dim r As Range
'0-Zeilen löschen
For Each r In .Range("E18:E46")
If r.Value = 0 Then
.Range("A" & r.Row & ":G" & r.Row).ClearContents
End If
Next r
End With
'** Tabellenblatt Abgabe Kopieren
Sheets("Auszahlung").Copy Before:=Sheets(2)
With ActiveSheet
'** Tabellennamen vergeben
.Name = tbaus
'** Zellen E18:E46 löschen
.Range("E18:E46").ClearContents
'** Zellen F18:F46 löschen
.Range("F18:F46").ClearContents
'** Zelle H11 Inhalt löschen (Auszahlungsdatum)
.Range("H11").ClearContents
'** Zellen E18:E46 Füllen aus der Tabellenblatt Abgabe / OHNE LEERZEICHEN
.Range("E18:E46").FormulaR1C1 = "=IF(" & tbab & "!RC="""",""""," & tbab & "!RC)"
'** Zellen A18:A46 Füllen aus dem Tabellenblatt Abgabe
'** Wenn in dem neuen Tabellenblatt Abgabe in dem Feld A18 nichts drin steht dann schreib  _
auch nix, wenn was drin steht dann hole den Inhalt
'    .Range("A18:A46").FormulaR1C1 = "=WENN(" & tbab & "!RC='';'';" & tbab & "!RC)"
'SORRY - statt den Fehler in der Formel zu suchen, habe ich die Bereiche einfach per VBA ü _
bertragen. Im Prinzip hast du es richtig gemacht, nur irgendwo einen Tippfehler
.Range("A18:A46").Value = Sheets(tbab).Range("A18:A46").Value
'AUCH HIER LEERZEILEN LÖSCHEN - sonst bleibt die "Größe" der Jogginghose stehen
For Each r In .Range("E18:E46")
If r.Value = "" Then
.Range("A" & r.Row & ":G" & r.Row).ClearContents
End If
Next r
End With
'BEIDE SHEETS: Formeln gegen absolute Werte tauschen - sonst verschwimmen die Bezüge beim  _
sortieren
With Sheets(tbaus)
.Range("A18:H46").Value = .Range("A18:H46").Value
End With
With Sheets(tbab)
.Range("A18:H46").Value = .Range("A18:H46").Value
End With
'BEIDE SHEETS: sortieren, um Leerzeilen weg zu bekommen.
With Sheets(tbaus)
'Zellenverbund aufheben - denn mit dem kann nicht sortiert werden!
.Range("A18:C46").UnMerge
'nach Spalte A sortieren - damit die Leerzeilen mittendrin raus fliegen
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("A18:A46"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
.Sort.SetRange .Range("A18:H46")
.Sort.Header = xlGuess
.Sort.MatchCase = False
.Sort.Orientation = xlTopToBottom
.Sort.SortMethod = xlPinYin
.Sort.Apply
End With
With Sheets(tbab)
'Zellenverbund aufheben - denn mit dem kann nicht sortiert werden!
.Range("A18:C46").UnMerge
'nach Spalte A sortieren - damit die Leerzeilen mittendrin raus fliegen
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("A18:A46"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
.Sort.SetRange .Range("A18:H46")
.Sort.Header = xlGuess
.Sort.MatchCase = False
.Sort.Orientation = xlTopToBottom
.Sort.SortMethod = xlPinYin
.Sort.Apply
End With
End Sub

schönes Wochenende!
23.01.2015 14:53:31
Klaus
Hi Rene,
ich schaue übers Wochenende nicht rein, bis Montag ist dieser Thread in der Versenkung. Wenn noch was ist, mach bitte ein neues Thema auf.
schönes Wochenende,
Klaus M.vdT.

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige