Office: Tabellenblatt kopieren Problem

In folgendem Code tritt immer derselbe Fehler auf und ich kapiere den Fehler absolut nicht.

Code:

Option Explicit
Public Dateiname, NewName As String
Public LUV As Workbook, Paten As Workbook
Sub einfuegen()
Application.StatusBar = "Daten werden verarbeitet..."
'Application.DisplayAlerts = False
Application.ScreenUpdating = False
' I=Zeile Patenliste;J=Zeile LUVliste;L=Zähler Loop String lesen
Dim arrTab, Pos, Pos1, Pos2, Pos3, mont, mont1, mont2, mont3, vers, SD, geh1, geh2, geh, leer, i, J, K, MO1, L, ypat, yluv, wpat, wluv, yy, X, Z, sj, fer, einl1, einl2, einl3, LetzteZeile As Integer
Dim Dateiname1, Datei, mpat, mluv, Text, Begriff, Suchen, Wert, w1, w2, jo, w3, strMldg As String
Set LUV = ThisWorkbook
' Prüfe ob Dateiname noch vorhanden, wenn nicht dann lese neu ein und speichere in Zeile 222 Spalte 2
Dateiname = LUV.Sheets("Sheet1").Cells(222, 2)
Text = Dateiname
Pos = InStr(26, Text, "\")
   If Pos Then
   Suchen = Mid(Text, Pos + 1, Len(Text))
    Else
    Suchen = Begriff
    End If
If Dir(Dateiname) = Suchen Then
Set Paten = GetObject(Dateiname)
Else
Datei = Application.GetOpenFilename("Micrsoft Excel-Dateien (*.xls),*.xls,Textdateien (*.txt),*.txt,Add-In-Dateien (*.xla),*.xla")
LUV.Sheets("Sheet1").Cells(222, 2) = Datei
Set Paten = GetObject(Datei)
End If
NewName = "KW " & KW(Date)
    If Blatt_vorhanden(NewName) Then
LUV.Worksheets("sheet1").Rang("A1").Copy Destination:=LUV.Worksheets(NewName).Cells(1, 1)
    Else
    'Sheets("Sheet1").Activate
            LUV.Worksheets("sheet1").Range("A1").Copy After:=Sheets("Sheet1")
            ActiveSheet.Name = NewName
     End If
........
........

Sub Öffnen()
    'ggf. Laufwerk und Ordner als Vorgabe setzen
    ChDir "t:\V1\V11\V11 all\H141\LUV\"
    ChDrive "t"
    'Das Dialogfenster
    Dateiname = Application.GetOpenFilename("Micrsoft Excel-Dateien (*.xls),*.xls")
    If Dateiname = False Then Exit Sub
    MsgBox "neuer Dateiname:" & vbNewLine & Dateiname
End Sub
Function KW(d As Date) As Integer
    Dim t As Date
    t = DateSerial(Year(d + (8 - Weekday(d)) Mod 7 - 3), 1, 1)
    KW = (d - t - 3 + (Weekday(t) + 1) Mod 7) \ 7 + 1
End Function
Public Function Blatt_vorhanden(strName As String) As Boolean
    Dim wksBlatt As Worksheet
Workbooks("LUV-Auftragstermine MO1_mit_Makro.xls").Activate
    For Each wksBlatt In ActiveWorkbook.Worksheets
        If wksBlatt.Name = NewName Then Blatt_vorhanden = True: Exit For
    Next
End Function

An der Stelle an welcher via:

Code:

NewName = "KW " & KW(Date)
    If Blatt_vorhanden(NewName) Then
   ' ActiveSheet.Copy Destination:=Worksheets(ActiveSheet.NewName)
ActiveSheet.Copy Destination:=Worksheets(NewName).Range("A1")
'ActiveSheet.Name = NewName
    Else
         ActiveSheet.Copy After:=Sheets("Sheet1")
         ActiveSheet.Name = NewName
    End If

Das Blatt kopiert werden soll entweder als neues Blatt oder als schon bestehendes Baltt mit den neuen Daten bekomme ich einen Laufzeitfehler 9...., egal was ich für eine Syntax verwende, selbst wenn ich das mit dem Makro Recorder aufzeichne einbaue und laufen lasse...., also hängt das vermutlich mit der Dimensionierung zusammen, aber ich komme nicht drauf wo ich falsch denke... :cry:

Vielen Dank für die Hilfe!!

 

Hallo AndySmith,

habe ich recht, dass der Laufzeitfehler in folgender Codezeile auftritt:

Code:

  If Blatt_vorhanden(NewName) Then

1) In der VBA-Hilfe ist unter anderem Folgendes dazu zu finden:

Die ersten 2 Zeilen sind zutreffend, die 3. Zeile ist für unser Problem eher irreführend.

2) Der Fehler tritt in der Funktion Blatt_vorhanden(...) auf. Da dort keine Fehlerbehandlung enthalten ist ("On Error ..."), wird diese Funktion abgebrochen, die Programmausführung springt zurück an den Ort, von wo sie aufgerufen wurde (durch die Function), dort aber ist auf der Ebene der Sub Einfuegen() auch keine Fehlerbehandlung aktiv (oberste Ebene), sodass die Programmausführung abbricht.

3) In der Function Blatt_vorhanden(..) gibt es 1 Auflistungsobjekt, nämlich Workbooks: um ein einzelnes Objekt aus der Liste auszuwählen, kann man entweder seine Nr. in der Liste verwenden oder aber seinen Namen. Damit das aber auf eine der beiden Arten funktioniert, muss dieses Objekt in der Auflistung existieren.
Indem man irgendwo vorher im Code in der Sub einfuegen zB. mit

Code:

Dim Wb as Workbook
Set Wb=Workbooks.Open("..Pfad..\LUV-Auftragstermine MO1_mit_Makro.xls")

dieses Workbook in den Arbeitsspeicher holt, wird es von Excel auch in die Auflistung Workbooks (mit dem Dateinamen als Schlüsselbegriff in der Auflistung) eingefügt
oder aber man öffnet diese Exceldatei händisch, indem man im Explorer einen Doppelklick auf sie ausführt - dann existiert sie natürlich auch in der Auflistung Workbooks als Workbook namens "LUV-..."