Office: Tabellenblatt umbenennen mit Namen aus Zelle

Hallo Excel Spezialisten,

da ich absoluter VBA Anfänger bin möchte ich euch gerne um Hilfe bitten.

Ich möchte dass ein Tabellenblatt aus einer Datei mit vielen Tabellenblättern automatisch beim Speichern den Namen aus Zelle A2 (Benutzername) dieser Tabelle bekommt.

Es soll so sein, dass egal in welchem Tabellenblatt sich der Benutzer zur Zeit befindet, beim Speichern das Tabellenblatt (hier mit Name Tabelle4) in den Benutzernamen der in dieser Tabelle4 in Zelle A2 hinterlegt ist umbenannt wird.

Dazu habe ich folgenden Codes hier im Forum gefunden und in „Diese Arbeitsmappe“ kopiert.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveWorkbook.Sheets(Tabelle4).Name = Range("A2").Value
ActiveWorkbook.Close

End Sub

Beim Schließen der Datei erhalte ich jedoch den Fehler:

Laufzeitfehler ‘13‘:
Typen unverträglich

Dann habe ich einen anderen Code ausprobiert den ich auch hier im Forum gefunden habe.

Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Tabelle4").Select
ActiveSheet.Name = Range("A2").Value
ActiveWorkbook.Close

End Sub

Dieser führt aber zu dem Fehler.

Laufzeitfehler ‘9‘:
Indes außerhalb des gültigen Bereichs

Dann habe ich noch einen Code aus dem Forum ausprobiert:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActivesheetSheet.Name = Range("A2").Value
ActiveWorkbook.Close
End Sub

Wenn ich diesen Code nutze und speichern will erhalte ich folgenden Fehler:

Laufzeitfehler ‘424‘
Objekt erforderlich.

Was ist hier falsch? Alles diese Codes wurde im Forum anderen Benutzern hierzu mitgeteilt und diese haben funktioniert.


Wenn Ihr das Problem lösen könnt, wovon ich als Laie mal ausgehe *Smilie , hätte ich noch eine weitere Frage bzw. Bitte.

Nachdem das Tabellenblatt den gewünschten Namen (Benutzername) hat, würde ich dieses auch gleich beim Speichern automatisch in eine andere Datei Kopieren (nur die Werte - Keine Formeln).

Ich hoffe Ihr könnt mir hier weiterhelfen.

Für eure Bemühungen bedanke ich mich im Voraus.

Gruß Manfred

Als Anlage mal drei Muster Dateien mit den Codes.

:)

 

Hallo Manfred,

Beim Speichern.

Code:

So kann es beim Schließen.
Code:

 

Hallo Harry,

danke für deine Antwort, werde dies gleich mal ausprobieren und mich dann wieder melden.

Gruß Manfred

Hallo Harry,

habe deinen Code ausprobiert. Leider funktioniert dieser aber nur wenn ich direkt in diesem Tabellenblatt bin und von dort aus die Datei schließe.

Wenn ich nun den Code wie folgt anpasse:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Tabelle4").Activate
With ActiveSheet
.Name = .Range("A2")

End With
ThisWorkbook.Save
End Sub

funktioniert dein Code einwandfrei.

Allerdings habe ich dann bei dem nächsten Schließen wieder eine Fehlermeldung da es da die "Tabelle4" ja nicht mehr gibt.

Hast du vielleicht noch eine weitere Idee wie es funktionieren könnte?

Gruß Manfred

 

Hallo Manfed,

du darfst das Sheet dann natürlich nicht mit dem Tabellennamen ansprechen sondern mit der "Sheetnummer", Da sich der Tabellenname ja ändert.

Anbei eine funktionierende Variante mit Harry´s Code.

Gruß
Norbert

 

Guten Morgen Norbert,

vielen Dank für deine Unterstützung, jetzt funktioniert es *Smilie ,

Hättest du evtl. auch eine Idee wie sich der zweite Teil meiner Frage realisieren lässt?

Für deine weitere Unterstützung bedanke ich mich im voraus.

Gruß Manfred

 

In eine bereits bestehende oder eine neue Datei? Wenn in eine bereits bestehenden dann wäre es sinnvoll den Zweck zu wissen - Auswertung, Zusammenführung, Update eines Tabellenblattes mit neuen Werten in einer bestehenden Tabelle oder einfach nur das Blatt kopieren und in einer bestehenden hintenanstellen? Was willst du dadurch erreichen?

Gruß
Norbert

 

Hallo Norbert,

dieses Tabellenblatt, welches ja in den Benutzernamen umbenannt wird, enthält eine Zusammenfassung einzelner Stati aus den anderen Benutzer Tabellen.

Dieses Blatt (nur der Inhalt, nicht die Formeln) soll in eine bestehende Datei kopiert werden die einem anderen Kollegen gehört und dient diesem lediglich als Update Status Information von den einzelnen Kollegen.

In dieser Datei wird nichts weiter ausgewertet, hat also nur einen reinen Info Charakter und kann beim nächsten Speichern einfach ersetzt werden.

Einen Code wie ein Tabellenblatt in eine andere Datei kopiert wird, habe ich schon hier im Forum gefunden. Allerdings werden da die Formeln mit kopiert, was aber wegen der dann vorhandenen Verknüpfung nicht sein muss.

Gruß Manfred

 

Hallo Manferd,

dann setze mal diesen Code ein:

PHP:

         Dim Name As String
Dim Mappe2 As Workbook
Dim NeueTabelle As Worksheet

With ThisWorkbook.Worksheets(4)
.Name = .Range("A2")
End With

'kopieren
Name = ThisWorkbook.Worksheets(4).Name
ThisWorkbook.Worksheets(4).Cells.Select
Selection.Copy

'Mappe öffnen und einfügen
Set Mappe2 = Workbooks.Open("Hier dein Link zum Workbook")
Set NeueTabelle = Mappe2.Sheets.Add
NeueTabelle.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlFormats
NeueTabelle.Name = Name
NeueTabelle.Range("A1").Select

speichern und schließen
Mappe2.Close savechanges:=True
ThisWorkbook.Save


 

Hallo Norbert,

Vermeiden Sie "Select und Selection".
In den meisten Fällen erlässlich. *wink.gif*

Code:

 

Vorab schon mal vielen Dank an Norbert und Harry,

ich werde das mal Ausprobien und euch mein Test Rückmelden. Werde aber erst Morgen dazukommen dies zu Testen.

Gruß Manfred

 

Hallo Norbert und Hallo Harry,

ich habe nun beide Codes von euch ausprobiert und habe folgende Probleme damit. Das muss aber nicht an euren Codes liegen, ich denke vielmehr ich bin nicht in der Lage diese richtig einzusetzten.

Norberts Code:

Soweit funktioniert alles gut, aber nur wenn ich direkt aus der Tabelle4 die Datei schließe. Die Tabelle wird dann korrekt unbekannt und in die andere Datei kopiert. Beim nächsten Speichern der Datei erhalte ich aber folgenden Fehler:

Laufzeitfehler 1004
Kann einem Blatt nicht den gleichen Namen geben wie einem anderen Blatt, einer Objektbibliothek oder einer Arbeitsmappe, auf die Visual Basic Bezug nimmt.

Bleibt dann bei „NeueTabelle.Name = Name“ hängen.

Will ich diese Datei aber aus einer anderen Tabelle aus speichern, erhalte ich den Fehler:

Laufzeitfehler 1004
Die Select-Methode des Range-Objekts konnte nicht ausgeführt werden.

Bleibt dann bei „ThisWorkbook.Worksheets(4).Cells.Select“ hängen


Harrys Code:

Schließe ich diese Datei direkt aus dem Tabellenblatt, wird dieses zwar unbenannt aber auch die Formeln in dieser Datei werden gelöscht, und es wird in der anderen Datei 1 neues Tabellenblatt - allerdings ohne den gewünschten Namen und ohne Inhalt angelegt.

Ich kann aber ohne Fehler diese Datei mehrfach hintereinander aus dem Tabellenblatt schließen, es wird jedes Mal eine neue Tabelle ohne Namen und Inhalt angelegt bzw. hinzugefügt.

Beim Schließen der Datei aus einer anderen Tabelle heraus, erhalte ich aber den gleichen Fehler wie bei Norbert:

Laufzeitfehler 1004
Kann einem Blatt nicht den gleichen Namen geben wie einem anderen Blatt, einer Objektbibliothek oder einer Arbeitsmappe, auf die Visual Basic Bezug nimmt.

Bleibt dann bei „ .Name = ThisWorkbook.Worksheets(4).Name“ hängen

Was mache ich hier falsch oder was muss ich evtl. noch an meine Dateien anpassen?

 

Versuche es mal Manfred.
Code:

Kürzere Code und stärker in die Suche von Blattnamen, wenn sie vorhanden sind oder nicht.
Code:

 

Hallo Harry,

einfach SUPER, beide Codes funktionieren genau so wie ich es mir vorgestellt habe. *hands

Ich danke dir für deine Hilfe und deine Nachsicht mit mir als VBA Noob.

Auch besten Dank an Norbert für deine Hilfe und deine Nachsicht.

Ihr beide habt es Echt drauf mit VBA. *top

 

Hallo Norbert,

so was nennt man spitze was ihr da macht. Hab das Ganze auch für mich gebrauchen können. Ist es auch möglich Grafiken mit zu übernehmen?
Den mit dem Code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets(4)
.Name = .Range("A2")
End With
'Mappe öffnen und einfügen
Workbooks.Open ("C:\Downloads\Test\DateiEmpfang.xls")
With ActiveWorkbook
If IsError(Evaluate(ThisWorkbook.Sheets(4).Range("A2") & "!A1")) Then
.Sheets.Add , .Sheets(.Sheets.Count)
.ActiveSheet.Name = ThisWorkbook.Sheets(4).Range("A2")
End If
With ActiveWorkbook.Sheets(ThisWorkbook.Sheets(4).Name)
ThisWorkbook.Worksheets(4).UsedRange.Copy
.Range("A1").PasteSpecial xlPasteValues
.Range("A1").PasteSpecial xlFormats
End With
'speichern und schließen
.Close True
End With
ThisWorkbook.Save
End Sub

wird die Grafik von mir nicht übernommen.

Viele Grüße
Vasek

 

Hallo Vasek,

Da Sie mir eine Private Nachricht gesendet haben.

Versuche es mal.

Code: