Office: Automatisches Makro um Sheets zu erstellen und zu benennen

Hallo Community,

ich versuche nun schon seit laengerem verzweifelt ein Makro zu erstellen, welches meinen Anforderungen entspricht. Leider bis jetzt nur mit Teilerfolgen. Deswegen wende ich mich an Euch.

Hatte hier im Forum schon einiges zu dem Thema gefunden und habe auch schon teilerfolge gehabt, allerdings bin ich noch nicht am Ziel.

Ich moechte ein Makro erstellen, welches zunaechst einmal nur durch die Eingabe in den Zellen E10-E40 ausgefuehrt wird. Also, falls ich in E10 was eingebe, falls in E11, usw. allerdings immer nur fuer die Zelle, in die gerade etwas eingegeben wurde.

Nach der Ausfuehrung durch Eingabe soll ein neues Worksheet nachrangig erstellt werden mit dem Titel der Eingabe und der Formatierung aus den Zellen A57:L71 (bzw. deer Formatierung aus einem Mastersheet ~Sheet2 A1-L14)

Ich war schon so weit, dass durch Eingabe in Zelle E10 ein sheet mit dem Titel der Eingabe "Mike" erstellt wurde und die Formattierung uebernommen wurde die ich unter die Tabelle gesetzt habe (schoen waere es die Formattierung von einem Mastersheet nehmen zu koennen).

So, nun weiss ich aber nicht mehr weiter und hoffe, dass mir hier jemand weiterhelfen kann.

Vielen Dank schon mal fuer Eure Muehen.

Viele Gruesse,
Sebastian

 

Re: Automatisches Makro um Sheets zu erstellen und zu benenn

Soweit ich das verstehe, brauchst Du primär das "Change"-Ereignis.

In diesem wird als erstes überprüft, ob es durch eine Änderung im Bereich e10:e40 aufgerufen wurde. Da wäre "intersect" das passende Stichwort.

Dann soll ein neues Blatt erstellt werden. Die Methode "worksheets.add" ist hier das Stichwort. Mit "n=cells(y,x).value" kann man den Inhalt einer Zelle in eine Variable auslesen und mit "activesheet.name=n" den Namen eines Blatts verändern.

Zuletzt soll in das neue Blatt die Formatierung aus einem anderen Blatt einkopiert werden. Das kriegt man mit ".copy" und ".pastespecial" hin.

Wenn man auf alle Sicherheitsüberprüfungen verzichtet, sind das alles in allem nur wenige Zeilen Source:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    Dim n As String

    If Not Application.Intersect(Target, Range("e10:e40")) Is Nothing Then

        n = Target.Value

        Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))

        ws.Name = n

        Worksheets("Tabelle1").Range("e10:e40").Copy
        Worksheets(n).Range("e10:e40").PasteSpecial Paste:=xlPasteFormats

    End If

End Sub

Die Formatierung wird jetzt hier aus dem Blatt "Tabelle1" und dem Bereich "e10:e40" genommen und auf das neue Blatt in den Bereich "e10:e40" einkopiert. Das müßtest Du nur auf die aktuellen Gegebenheiten anpassen.

Allerdings würde ich zumindest die Variable "n" noch einigen Überprüfungen unterziehen, weil es sonst leicht zu blöden Fehlermeldungen kommen kann, wenn das neue Blatt nicht erstellt werden kann.

Zuerst einmal würde ich mit n=trim$(n) ggf. Leerzeichen am Anfang und Ende entfernen. Und dann sollte man noch mit replace(...) das eine oder andere Zeichen aus n entfernen oder ersetzen, wie z.B. den / oder so.

Gruß, Michael

 

Wow, tausend dank Michael!

Du hast mir sehr weitergeholfen, es klappt einwandfrei. Werde auch Deinen Rat annehmen und noch ein wenig was dazu bauen.

 

... leider doch nicht ganz einwandfrei.

Er uerbnimmt zwar die farbliche Formatierung in die neu erstellte Mappe auf, aber weder Text noch die Hoehe und Breite der einzelnen Zellen. Kann mir da noch einmal jemand helfen?

 

Text wäre ja auch "Inhalt", das wird bei "Formatierung" eben gerade nicht übernommen. Das mit der Höhe... Hmmm... Ok, vielleicht kleiner Trick:

Man kann ja erstmal "alles" kopieren, also neben der Formatierung auch den Inhalt. Dadurch paßt Excel vielleicht auch gleich die Zeilenhöhe an (hab ich jetzt nicht ausprobiert). Und danach löscht man eben wieder die Zellen-Inhalte, die man nicht haben will.

Das wäre dann so in der Art:

Code:

s1.range(r1).copy destination:=s2.range(r2)
s1.range(r3).clearcontents

Mit der ersten Zeile wird alles, also Text-Inhalt, Formeln, Formatierungen usw., von dem einem Blatt (s1) aus dem Bereich r1 auf das 2. Blatt (s2) in den Bereich r2 kopiert.
In der 2. Zeile wird dann der Inhalt des Bereichs r3 auf dem 2. Blatt gelöscht. Dabei bleiben aber die vorher kopierten Formatierungen erhalten.
Für die Variablen kannst Du dann ja sowas setzen wie:

Code:

dim s1 as worksheet
dim s2 as worksheet
dim r1 as string
dim r2 as string
dim r3 as string
set s1=thisworkbook.worksheets("Musterevorlage")
set s2=thisworkbook.worksheets("Neues Blatt")
r1="e10:e40"
r2="a1:a31"
r3="e12:e38"

Alternativ kannst Du Dich natürlich auch per Code um das Finetuning der Formatierung (Zeilenhöhe und Spaltenbreite z.B.) kümmern:

Code:

s2.columns("e").columnwidth=25
s2.rows("10:40").rowheight=20

Oder auch eine Überschrift im neuen Bereich vorgeben:

Code:

s2.range("e10").value="Werte:"

Je mehr solcher Einstellungen Du aber nach dem Erstellen eines neuen Blattes machen willst / mußt, desto mehr macht es irgendwann Sinn, nicht ein neues leeres Blatt zu erstellen, sondern eine Vorlage mit allen Einstellungen zu kopieren.

Gruß, Michael

 

Genau das waere glaub ich das richtige - eine Vorlage die nur kopiert wird. Ich hab in sheet 1 ein Liste, die bei jeder neuen Eingabe ein neues Sheet erstellt, mit dem Titel der Eingabe. Das Layout und der Text, sowie alle Formatierungen sollen auf allen neuen Sheets identisch sein. Wie stelle ich das an?

 

Eigentlich ganz simpel:

Zuerst erstellst Du Dir ein Blatt in der Mappe, das als "Muster" dienen soll. Dort packst Du also alles an Formatierungen, Beschriftungen, Rahmen, Farben usw. rein, was später auch in den anderen Blättern zu sehen sein soll.

Dann kannst Du das Blatt ausblenden, z.B. über "Format - Blatt - ausblenden".

Den Code, den ich Dir zuerst genannt hatte, gibt es dann in leicht abgewandelter Version. Die alten Zeilen habe ich mal zur Referenz mit ' als Kommentar in der Routine drin gelassen:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    Dim n As String

    If Not Application.Intersect(Target, Range("e10:e40")) Is Nothing Then

        n = Target.Value

        'Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
        Worksheets("Vorlage").Copy after:=Worksheets(Worksheets.Count)
        Set ws = Worksheets(Worksheets.Count)

        ws.Name = n

        ws.Visible = True

        'Worksheets("Tabelle1").Range("e10:e40").Copy
        'Worksheets(n).Range("e10:e40").PasteSpecial Paste:=xlPasteFormats

    End If

End Sub

Die Zeile ws.visible=true ist nur notwendig, wenn Du die Mustervorlage ausgeblendet hast. Wenn nicht, schadet sie allerdings auch nicht.

Ansonsten ändert sich nur:
a) Aus dem .add wird ein .copy
b) Das Übertragen der Formatierung entfällt

Gruß, Michael

 

Tausend Dank, bis zur naechsten Frage hast Du mir schon einmal sehr geholfen!