one VBA macro used to work. Suddenly Gives error

[netanel99]

hello experts,

I used this macro for a long time in many excel files , now all of a sudden , it stopped working and after I click the button it gives path/file access error (with a changin .tmp file name every time) and then after I click ok it gives another error runtime error 2147352565 (8002000b) - can't copy this sheet
and all of this happened out of the blue, 100/100 on the desperate meter , is there any clue for what the problem is ?

Essentially the code copy-paste a template sheet and renaming it to the inputbox the user've entered.
Sheets(strName).Select near the end of the code in this line is where I get the yellow mark of error

thanks for any help ,
Netanel

 Option Explicit

Private Sub CommandButton1_Click()


    Dim ws As Excel.Worksheet
    Dim strName As String
 
    On Error GoTo ErrHandler
 
    strName = Application.InputBox("Please enter the  description...", "New Item", Type:=2)
 
    If strName <> vbNullString Then
        strName = ValidSheetName(strName)
    Else
        MsgBox "Cancelled...", vbInformation
        Exit Sub
    End If
 
    If SheetExists(strName) Then
        MsgBox "You already have a description called " & strName, vbExclamation, "Error"
        Exit Sub
    End If
     
    '// Turn off screen updating...
    Application.ScreenUpdating = False
 


    With Sheets("template")


        .Visible = xlSheetVisible


        .Copy after:=Sheets(Sheets.Count)


    End With
 
 
    Set ws = ActiveSheet
 
    With ws
        .Name = strName
 
        .Visible = xlSheetVisible


        .Range("StrategyName").Value = strName
    End With
 
    '// Add a row to the TOC table
    Dim oNewRow As ListRow
    Set oNewRow = ListObjects("StrategyTOC").ListRows.Add(AlwaysInsert:=True)
 
    With oNewRow
        .Range.Cells(1, 2).Value = strName
    End With


ErrHandler:
 
    Sheets("sheet for Contents").Activate


    Application.ScreenUpdating = True
                   
    Sheets(strName).Select         'in this line I get the yellow mark of error
 
 
End Sub

 

[Marc L]

Hi,​

​

you must investigate first in debug step-by-step mode hitting F8 key to find out which codeline raises the error​

or this thread as it is seems to be just a guessing challenge for some mind readers forum …​

 

[netanel99]

thanks Marc ,
the error inside the code is on the line " Sheets(strName).Select " near the end in the code

but I did what you said pressed F8 it also sent me to 2 functions I completely forgot about , which the code uses.

Option Explicit
Option Compare Text

Public Function ValidSheetName(sSheetName As String) As String
    
    Const csInvalidChars As String = ":\/?*[]"
    
    Dim lThisChar As Long
    
    
    ValidSheetName = sSheetName
    For lThisChar = 1 To Len(csInvalidChars)
        ValidSheetName = Replace$(ValidSheetName, Mid(csInvalidChars, lThisChar, 1), "")
    Next
    
    ValidSheetName = Left$(ValidSheetName, 31)
    
End Function

Public Function SheetExists(strName As String) As Boolean

    Dim ws As Excel.Worksheet
    
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(strName)
    
    SheetExists = (Err = 0)
    Set ws = Nothing
End Function

 

[Marc L]

According to the codine where the error occurs :​

​

the sheet can't be selected as its workbook is not active or the sheet name is not valid or the sheet does not exist …​

 

[netanel99]

wow, it used to work just fine in multiple excel files
breaking and scratching my mind right now..

I'm thinking maybe I used other macro on other file that ruined it ,
for example something like
Application.ScreenUpdating =false
but something else, that've ruin this specific code.
reviewing my last macros on other files right now

I think the fault is in ".Visible = xlSheetVisible"
something there not right or something like this. trying hoping to guess it right

I think something not visible that's why it isn't find it maybe..

 

[Joe4]

What is the value of "strName" when you get the error?
Are you sure that a sheet by that name actually exists in your active workbook.
Note that is must match EXACTLY.

 

[netanel99]

Thanks for the help Joe!

I'm a beginner so I'll try to VBA talk with you experts,
StrName is based on what the user type in the MsgBox, I have no idea why the error occure, maybe something relate to hidden sheet ?
I just trying to figure things out

 

[Joe4]

I'm a beginner so I'll try to VBA talk with you experts,
StrName is based on what the user type in the MsgBox, I have no idea why the error occure, maybe something relate to hidden sheet ?
I just trying to figure things out

Yes, I am asking what exact value are they entering in for "strName" that causes the error?
Maybe they are entering in a value that contains illegal characters to be used in a sheet name.
But we cannot know if you don't tell us what is being entered in when this error occurs!

 

[netanel99]

sure, well it isn't anything illegal , I've tested something as simple as GG or dia or try . everything gives the same error.

 

[netanel99]

pls, it's not something wrong about the code for sure, I've this code in 10 different excel files,
suddenly everything stopped working.
the cause is somethign more generic .