Macro runs in debug mode perfectly fine, but when i try to run it normally it errors out

[Xalova]

Hello all.

The title basicly says it all. I have a macro which just adds sheets with a desired name. When i wrote the code i looked if it works as intended in debug mode. But as soon as i wanted to run the macro normally it errors out.

the code that i run is as follows:

Sub killast()

With Worksheets("vorlage").Range("anzahltage2")
Dim kill As Integer
If .Value > 2 Then
sheetname = Sheets(Sheets("Vorlage").Index + 1).Name
kill = MsgBox("Willst du wirklich Das kalkulationsblatt ""K(" & .Value - 1 & ")""löschen?", vbQuestion + vbYesNo + vbDefaultButton1)
If kill = vbYes And .Value > 1 Then
sn = "K(" & .Value - 1 & ")"
Application.DisplayAlerts = False
Worksheets(sn).Unprotect
Sheets(sn).Delete
'Sheets("K(" & .Value - 2 & ")").Activate
Application.DisplayAlerts = True
Worksheets("vorlage").Unprotect
.Value = .Value - 1
Worksheets("vorlage").Protect

Else
Exit Sub
End If
Else
sn = Sheets(Sheets("Vorlage").Index + 1).Name
kill2 = MsgBox("Willst du wirklich die daten aus dem blatt """ & sn & """löschen?", vbQuestion + vbYesNo + vbDefaultButton1)
    If kill2 = vbYes Then
        Application.DisplayAlerts = False
         Sheets(sn).Unprotect
         Sheets(sn).Delete
        Application.DisplayAlerts = True
           Call Copyrenameworksheetss
    End If
End If
End With
End Sub

the code which gets called:

Sub Copyrenameworksheetss()
'Updateby Extendoffice
    Dim ws As Worksheet
    Set wh = Worksheets(ActiveSheet.Name)
    With Worksheets("Vorlage").Range("AnzahlTage2")
    
    ThisWorkbook.Worksheets("Vorlage").Visible = True
        Sheets("Vorlage").Copy after:=Sheets(Sheets.Count)
    
    If IsNumeric(.Value) Then
    'ActiveSheet.Name = "Tag " & .Value
    If Not Sheets.Count - Sheets("Vorlage").Index = .Value Then
    Sheets("Vorlage").Unprotect
    .Value = Sheets.Count - Sheets("Vorlage").Index - 1
    Sheets("Vorlage").Protect
    End If
    If Sheets.Count - Sheets("Vorlage").Index = .Value + 1 Then
    Sheets("Vorlage").Unprotect
    .Value = Sheets.Count - Sheets("Vorlage").Index
    sn = "K(" & .Value & ")"
    Sheets("Vorlage").Protect
    End If
    sn = "K(" & .Value & ")"
    ActiveSheet.Name = "K(" & .Value & ")"
    'Sheets("Tag " & .Value).Tab.ColorIndex = 10
    Sheets(sn).Tab.ColorIndex = 10
    Worksheets("Vorlage").Unprotect
    .Value = .Value + 1
    Worksheets("Vorlage").Protect
    ThisWorkbook.Worksheets("Vorlage").Visible = False
    End If
    GoTo ciao
    End With
ciao:
End Sub

it errors out on

 Worksheets("Vorlage").Unprotect

 

[Alex Blakenburg]

Are you sure it is erroring out on:-

Worksheets("Vorlage").Unprotect

I can occasionally get it to fail on:-

Worksheets(sn).Unprotect

And that is because it is looking for a particular K say K(2) and it is not there.

Note: the only way Unprotect seems to be able to fail is for the sheet it is unprotecting to not exist.

I am bit concerned that by copying the sheet Vorlage you are also creating multiple copies of the range name "anzahltage2" which seems undesirable.

 

[Alex Blakenburg]

Oops apparently my K with an "n" was the code for a thumbs down emoji and I didn't notice.

 

[Xalova]

Oops apparently my K with an "n" was the code for a thumbs down emoji and I didn't notice.

yes i am absolutly sure. I just checked.

also if i run the macro in the vba manager(idk how its called tbh) it works too. Only if i try to run the code via a button, it errors out

 

[Alex Blakenburg]

And the code is definitely in the same workbook as the worksheets and is in a standard module not in a sheet or the workbook code module.
And does it work when you run it from alt+f8 ?
Perhaps add a line before that code:
debug.print activeworkbook.name

 

[Xalova]

And the code is definitely in the same workbook as the worksheets and is in a standard module not in a sheet or the workbook code module.
And does it work when you run it from alt+f8 ?
Perhaps add a line before that code:
debug.print activeworkbook.name

1st, yes. I am in the same workbook. I know thar because its the only workbook open.

2nd, i dont know what should happen, but when i press alt+f8 nothing happens at all.

3rd, it does not change anything if put

debug.print activeworkbook.name

before the errorcode

 

[MARK858]

Do you have a personal workbook and if yes is the code in it?

3rd, it does not change anything if put

debug.print activeworkbook.name

before the errorcode

You need to look in the Immediate window, if you can't see the Immediate window while in the VBE press Ctrl + G (you might need to expand/resize it)