Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1684to1688
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

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

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

303 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige