Kopieren trotz Blattschutz, Werte einfügen

Computer: Software & Programmierung Visual Basic
#1

Hallo zusammen,

ich habe mal wieder eine Frage.

Ich habe eine Tabelle mit Blattschutz, den ich auch leider nicht aufheben kann. Jetzt möchte ich das gesamte Tabellenblatt kopieren und in dem neuen Tabellenblatt nur die Werte einfügen, das Layout muss aber bestehen bleiben. Ich habe auch schon ein Makro, aber das läuft leider immer auf einen Fehler, obwohl es genau das macht, was ich möchte.

Hier das Makro:

Sub Tabellenblatt_kopieren_nur_Werte_2()
Dim wshUrsprungsblatt As Worksheet
Set wshUrsprungsblatt = ActiveSheet
ActiveSheet.Copy
ActiveSheet.UsedRange.Cells = ActiveSheet.UsedRange.Cells.Value
End Sub

Das Makro führt die letzte Zeile zwar aus, aber gibt dann auch immer den Fehler: Laufzeitfehler 1004, Anwendungs- oder Objektdefinierter Fehler aus. Ich hoffe, Ihr könnt mir weiterhelfen.

Ich habe auch noch eine Frage, kann man per Makro auf verbundene Zellen zugreifen?

LG

Martina

#2

Hallo Martina,

Ich habe eine Tabelle mit Blattschutz, den ich auch leider
nicht aufheben kann. Jetzt möchte ich das gesamte
Tabellenblatt kopieren und in dem neuen Tabellenblatt nur die
Werte einfügen, das Layout muss aber bestehen bleiben. Ich
habe auch schon ein Makro, aber das läuft leider immer auf
einen Fehler, obwohl es genau das macht, was ich möchte.

nein, es macht nicht genau das was du möchtest bzw. was im Code steht.
Es kopiert nach Wunsch alles in ein neues Blatt/Mappe, in der
letztenCodezeile soll der Code Formeln in Werte umwandeln.
Das darf/kann er aber nicht wegen Blattschutz, ergo kommt der Hinweis.
Und die Formeln bleiben Formeln.

Teste mal nachstehenden Code, Zeilenhöhen bzw. Spaltenbreiten muß man
noch nachträglich integrieren.

Ich habe auch noch eine Frage, kann man per Makro auf
verbundene Zellen zugreifen?

Prinzipiell ja, was haste denn genau vor?

Gruß
Reinhard

Sub Tabellenblatt\_kopieren\_nur\_Werte\_2()
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
 .UsedRange.Copy
 Workbooks.Add
 ActiveSheet.Range(.UsedRange.Cells(1, 1).Address).PasteSpecial Paste:=xlValues
 ActiveSheet.Range(.UsedRange.Cells(1, 1).Address).PasteSpecial Paste:=xlPasteFormats
 Application.CutCopyMode = False
 Range("A1").Select
End With
End Sub
#3

Hallo Reinhard,

sorry, dass ich erst jetzt antworte, aber ich war am Wochenende leider nicht am PC.

Vielen Dank für Deinen Code, ich habe ihn ausprobiert. Er funktioniert auch sehr gut, aber leider hat das Blatt nach dem Kopieren keine Blattschutz mehr, den brauche ich aber unbedingt. Ich muss eine Tabelle mit Blattschutz mit bestimmten Daten füllen. Dies möchte ich gerne mittels Formeln oder VBA machen. Nach der Bearbeitung der Tabelle, muss der Blattschutz aber immer noch auf der Tabelle liegen. Ich habe nun die Tabelle einmal ohne Blattschutz erstellt und mit Formeln gefüllt. Dann habe ich in der Tabelle mit Blattschutz die zu bearbeitenden Felder mit dem Verweis auf die Formeltabelle versehen und muss nun nur noch die Formeln aus der geschützten Tabelle entfernen. Deshalb mein Gedanke, die Tabelle zu kopieren und dann auch nur die Werte einfügen. Es müssen nämlich Layout und Blattschutz erhalten bleiben. Und ich muss die Vorlage immer wieder verwenden können. Leider gibt es in der Originaltabelle mehrere verbundene Zellen, darum die Frage bezüglich der verbundenen Zellen. Ich hoffe, ich habe mich einigermaßen verständlich ausgedrückt.

LG Martina

#4

Grüezi Martina

Vielen Dank für Deinen Code, ich habe ihn ausprobiert. Er
funktioniert auch sehr gut, aber leider hat das Blatt nach dem
Kopieren keine Blattschutz mehr, den brauche ich aber
unbedingt.

Dann setze doch einfach den Blattschutz am Ende des Codes wieder.
Die folgenden Zeilen kopieren das aktive Tabellenblatt mit Blattschutz ganz ans Ende der aktiven Mappe, heben den Blattschutz auf und ersetzen die Formeln durch Werte. Dann wird der Blattschutz wieder gesetzt.
Das klappt auch wenn der Blattschutz mit einem PW vergeben worden ist und die User bekommen vom Aufheben und wieder setzen auch nichts mit.

Public Sub tr\_CopySheet()
 ActiveSheet.Copy After:=Sheets(Sheets.Count)
 With ActiveSheet
 .Unprotect
 .UsedRange.Cells.Value = .UsedRange.Cells.Value
 .Protect
 End With
End Sub

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#5

Hallo Thomas,

vielen Dank für Deine Antwort. Vom Prinzip her funktioniert das Makro super, aber da ich das Paßwort für den Blattschutz nicht kenne, kann ich das Makro leider nicht verwenden. Das ist die Schwierigkeit des Ganzen. Ich möchte dies Tabellenblatt automatisch ausfüllen, da es händisch sehr viel Zeit in Anspruch nimmt, aber der Blattschutz ist so gesetzt, dass man nur wenige Zellen ausfüllen kann und ansonsten kann man mit der Tabelle leider nichts anfangen. Im Moment versuche ich es mit einem Makro, dass die freien Zellen mit Arrayformeln ausfüllt und dann auch gleich wieder löscht. Vielleicht klappt es ja damit.

Trotzdem nochmal vielen Dank für Eure Hilfe.

LG Martina

#6

Grüezi Martina

da ich das Paßwort für den Blattschutz
nicht kenne, kann ich das Makro leider nicht verwenden. Das
ist die Schwierigkeit des Ganzen.

Beim Aufheben oder Entfernen von Passwörtern können, dürfen und werden wir dir hier nicht weiterhelfen.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#7

Hallo Thomas,

das verstehe ich gut.

Trotzdem vielen Dank für Eure Hilfe.

LG Martina