Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.09.2025 10:29:46
Wert aus einer Zelle bei jeder Eingabe in neue Zelle
26.09.2025 08:51:00
Teilinhalt einer Zelle vergleichen
25.09.2025 23:04:12
Einen von drei möglichen Einträgen in einer Zeile suchen
25.09.2025 19:59:48
Lebenserleichternde Datei, im Bezug auf die Essensauswahl
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1684to1688
1684to1688
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Sheets kopieren, UsedRange.Value geht nicht

Forumthread: Sheets kopieren, UsedRange.Value geht nicht

Sheets kopieren, UsedRange.Value geht nicht
12.04.2019 10:31:41
Michael
Liebe Excel-Profis,
ich verzweifle gerade an einer (woanders funktionierenden) Formel und habe eventuell nur Tomaten auf den Augen oder bin als selbstversuchender Laie nur zu unerfahren.
Folgende Aufgabe:
Ich will per Makro mehrere Sheets kopieren, die Formeln durch Werte ersetzen und die Datei neu speichern.
Das (im Netz zusammengesuchte und dann angepasste) Makro funktioniert auch....bis auf das Auflösen der Formeln zum richtigen Zeitpunkt.
Ich habe zwei Varianten ausprobiert, beide funktionieren nicht: 

Private Sub Werte_Einfuegen()
Dim Dateiname As String
Dim WBS As Workbook
Dim WS As Worksheet
pfad = ActiveWorkbook.Path & "/"
Dateiname = "Dateiname_" & ActiveSheet.Cells(5, 3).Value
With Worksheets(Array("Hier stehen die Sheets"))
.Copy
Set WBS = ActiveWorkbook
For Each WS In WBS.Worksheets
WS.UsedRange.Value = WS.UsedRange.Value
Next WS
End With
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=pfad & Dateiname & ".xlsx"
End Sub

und 

With Worksheets(Array("Hier stehen die Sheets"))
.Copy
Set WBS = ActiveWorkbook
For Each WS In WBS.Worksheets
With Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Next WS
End With
Die Sheets werden kopiert, die Datei wird korrekt gespeichert, aber die verformelten Zellen zeigen nun alle #WERT.
Könnte es an der Art der Formeln liegen? Beispiel: 
=INDEX(Konsolidierung!$E$3:$AI$51;VERGLEICH(A7;Konsolidierung!$E$3:$E$51;0);  VERGLEICH(RECHTS(ZELLE("dateiname";$X$1);LÄNGE(ZELLE("dateiname";$X$1))-FINDEN("]"; ZELLE("dateiname"; $X$1)));Konsolidierung!$E$3:$AG$3;0)+1)

Wer kann mir einen Tipp geben?
Vielen Dank im Voraus!
Michael
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sheets kopieren, UsedRange.Value geht nicht
12.04.2019 11:05:43
Torsten
Hi Michael,
versuch mal: 

WS.UsedRange.Cells.Value = WS.UsedRange.Cells.Value

AW: Sheets kopieren, UsedRange.Value geht nicht
12.04.2019 11:06:28
EtoPHG
Hallo Michael,
In der Problembeschreibung Ich will per Makro mehrere Sheets kopieren, die Formeln durch Werte ersetzen und die Datei neu speichern. fehlen genauere Angaben, wie
mehrere Sheets welche, welche nicht?
WOHIN kopieren ?
Speichern oder Speichern unter?
Ich würde folgendermaßen vorgehen (wenn die Anforderung geändert wird auf: Eine Arbeitsmappe aus einer bestehenden erstellen, die nur einen Teil von Tabellenblättern und in letzteren nur Werte enthält.
1. Speichern unter neuem Namen
2. Öffnen der Mappe (neuer Name)
3. Umwandeln der betroffenen Blätter in Werte
4. Löschen der 'überflüssigen' Blätter
5. Speichern der Mappe
Gruess Hansueli
Anzeige
AW: Sheets kopieren, UsedRange.Value geht nicht
12.04.2019 11:19:50
Torsten
Hallo Hansueli,
lies mal richtig. Das musst du alles nicht wissen: 
Das (im Netz zusammengesuchte und dann angepasste) Makro funktioniert auch....bis auf das Auflösen der Formeln zum richtigen Zeitpunkt.

AW: Sheets kopieren, UsedRange.Value geht nicht
12.04.2019 11:26:30
EtoPHG
Hallo Torsten,
Was zum Teufel willst Du mir mitteilen?
Bei VBA bescheiden ist eine Aussage "Makro funktioniert auch....bis auf" mit grösster Vorsicht vor Fehlinterpretationen zu geniessen.
u.a. UsedRange ist eine äusserst gefährliche Range, da sehr dynamisch!
Die Reihenfolge der Kopiervorgänge kann entscheidend sein!
Tabellenübergreifende Formel-Referenzen können plötzlich ins Leere zeigen oder auf eine 'andere' Mappe verweisen.
Darum meinen Fragen, bzw. Anmerkungen. Da nützt mir alles richtig lesen nichts!
Gruess Hansueli
Anzeige
AW: Sheets kopieren, UsedRange.Value geht nicht
12.04.2019 13:07:54
Michael
Vielen Dank euch beiden für den Input und die schnelle Hilfe!
Ich habe jetzt zunächst mal 
WS.UsedRange.Cells.Value = WS.UsedRange.Cells.Value
ausprobiert, hat leider nicht funktioniert(keine sichtbare Veränderung). Die Idee von Hansueli gefällt mir sehr gut, ich habe sie mal ausprobiert und stehe jetzt vor einem anderen Problem. Mit dem Code von mir speichert das Makro jetzt die zu erstellende Datei als Masterdatei ab und benennt den Master in die Zieldatei um, obwohl es anders herum sein sollte:
Die Datei, in der das Makro ausgelöst wird, soll der Master bleiben, die neu erstellte Datei soll das erste Sheet und die Formeln gelöscht bekommen und als "Dateiname neu..." gespeichert werden.
An welcher Stelle kann ich das umdrehen?

Sub Schaltfläche3_Klicken()
Dim Dateiname As String
Dim WBS As Workbook
Dim WS As Worksheet
pfad = ActiveWorkbook.Path & "/"
Dateiname = "Dateiname neu_" & ActiveSheet.Cells(5, 3).Value & ".xlsm"
ActiveWorkbook.SaveCopyAs Filename:=pfad & Dateiname
Set WBS = ActiveWorkbook
For Each WS In WBS.Worksheets
WS.UsedRange.Cells.Value = WS.UsedRange.Cells.Value
Next WS
Application.DisplayAlerts = False
WBS.Sheets(1).Delete
WBS.Save
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub
Vielen Dank auch für den Input mit der Unschärfe der Aussage. Wenn ich hier zur Lösung noch etwas genauer angeben muss, bitte Bescheid sagen, dazu bin ich noch zu unerfahren, um zu definieren, welche Parameter da essentiell sind.
Liebe Grüße
Michael
Anzeige
.SaveAs anstelle von .SaveCopyAs ! (owT)
12.04.2019 13:21:07
.SaveCopyAs

AW: .SaveAs anstelle von .SaveCopyAs ! (owT)
12.04.2019 13:30:56
.SaveCopyAs
Super, Danke!
Und mit Workbooks.Open ist sogar mein Master wieder offen, wenn ich ihn nochmals brauche.
Schönes Wochenende!
Michael
;
Anzeige
Anzeige

Infobox / Tutorial

Sheets kopieren und Formeln durch Werte ersetzen

Schritt-für-Schritt-Anleitung

  1. Öffne deine Excel-Datei und drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Füge ein neues Modul hinzu, indem du im Menü auf Einfügen > Modul klickst.

  3. Kopiere und füge den folgenden VBA-Code ein:

    Sub SheetsKopierenUndWerteEinfügen()
    Dim Dateiname As String
    Dim WBS As Workbook
    Dim WS As Worksheet
    Dim pfad As String

    pfad = ActiveWorkbook.Path & "/"
    Dateiname = "Dateiname_neu_" & ActiveSheet.Cells(5, 3).Value & ".xlsm"

    ' Kopiere die Sheets
    Worksheets(Array("Hier stehen die Sheets")).Copy
    Set WBS = ActiveWorkbook

    ' Ersetze Formeln durch Werte
    For Each WS In WBS.Worksheets
        WS.UsedRange.Value = WS.UsedRange.Value
    Next WS

    ' Speichere die neue Datei
    WBS.SaveAs Filename:=pfad & Dateiname
    Application.CutCopyMode = False
End Sub

  4. Passe den Code an, indem du die Namen der Sheets in der Worksheets(Array(...))-Zeile ersetzt.

  5. Schließe den VBA-Editor und führe das Makro aus.

Häufige Fehler und Lösungen

  • Fehler: Formeln zeigen #WERT! an

    • Überprüfe, ob die Formeln in den Original-Sheets korrekt sind. Manchmal können ungültige Zellreferenzen beim Kopieren Probleme verursachen.

  • Fehler: Die Datei wird nicht korrekt gespeichert

    • Stelle sicher, dass der pfad korrekt gesetzt ist. Du kannst den Pfad manuell anpassen, falls nötig.

  • Lösung: Statt WS.UsedRange.Value = WS.UsedRange.Value

    • Probiere WS.UsedRange.Cells.Value = WS.UsedRange.Cells.Value, um sicherzustellen, dass alle Zellen abgedeckt sind.

Alternative Methoden

Wenn das oben genannte Makro nicht funktioniert, gibt es alternative Methoden:

  1. Verwendung von Copy und PasteSpecial

    WS.UsedRange.Copy
WS.PasteSpecial Paste:=xlPasteValues

  2. Kopieren und Einfügen von spezifischen Bereichen

    • Statt UsedRange kannst du einen spezifischen Bereich angeben, um Formeln nur in bestimmten Zellen zu ersetzen.

Praktische Beispiele

Hier ist ein einfaches Beispiel für ein Makro, das mehrere Sheets kopiert und alle Formeln durch ihre Werte ersetzt:

Sub BeispielMakro()
    Dim WBS As Workbook
    Dim ws As Worksheet

    Worksheets(Array("Sheet1", "Sheet2")).Copy
    Set WBS = ActiveWorkbook

    For Each ws In WBS.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws

    WBS.SaveAs Filename:="C:\Pfad\zu\deiner\Datei.xlsx"
End Sub

Tipps für Profis

  • Verwende UsedRange mit Bedacht: UsedRange kann dynamisch sein und sich ändern. Prüfe die genauen Zellreferenzen.
  • Fehlerbehandlung einbauen: Füge On Error Resume Next vor kritischen Zeilen hinzu, um den Code robuster zu machen.
  • Backup machen: Speichere immer eine Kopie deiner Datei, bevor du mit Makros arbeitest.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass keine Formeln verloren gehen? Du kannst eine Kopie der Arbeitsmappe erstellen und dort die Formeln durch Werte ersetzen.

2. Was ist der Unterschied zwischen SaveCopyAs und SaveAs? SaveCopyAs erstellt eine Kopie der Arbeitsmappe, während SaveAs die aktuelle Arbeitsmappe unter einem neuen Namen speichert.

3. Wie kann ich mehrere Sheets auf einmal bearbeiten? Du kannst den Namen der Sheets in der Worksheets(Array(...))-Zeile auflisten, um mehrere Sheets gleichzeitig zu kopieren und zu bearbeiten.

4. Welche Excel-Versionen unterstützen diese VBA-Funktionen? Die meisten VBA-Funktionen sind in Excel 2007 und späteren Versionen verfügbar.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige