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]

​

Just obviously check the variables contents according to the existing …​

 

[Joe4]

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 .

The most common causes are:
1. Corruption in the workbook
2. Being used on a different computer than does not have all the same VB References/Libraries selected
3. Something changed with the data structure of the files involved
4. Issue is with what user is inputting

Without having access to your file, it is very difficult for us to diagnose this. If you can upload the file(s) to a file share site, and provide a link to it, someone may be able to download your files and see what is happening. If you are able to do that, please also walk us through an exact sample for us to try, so that we are sure that we are doing the exact same thing you are when you get the error.

 

[netanel99]

sure, this is the file
link
I think that I'm the only one who get this error

1. Corruption in the workbook

not the case because I've checked files on differeny external hard disks and nothing worked.

2. Being used on a different computer than does not have all the same VB References/Libraries selected

not the case because Its private laptop , I don't use sharing options . only direct and use from the hard disk

3. Something changed with the data structure of the files involved

I don't know , maybe

4. Issue is with what user is inputting

not the case.


thank you for all, this is like a sudden death of one specific macro , so weird!

 

[Joe4]

I don't think you can rule out 1 and 4.

If you have tried it out on other external hard-drives, and it doesn't work there, that seems to support that workbook corruption could be an issue.
Can you get it to work at all right now? If there is not any scenario where you can get it to work right now, workbook corruption is a very real possibility.

Also, please provide what I asked for in my previous post:

please also walk us through an exact sample for us to try, so that we are sure that we are doing the exact same thing you are when you get the error.

Most importantly, exactly what are you entering into the Input Box?

 

[Marc L]

​

♪ Step-By-Step ! ♫​

 

[netanel99]

thanks a lot.
Did it work to you ? I've added the link to the file.

If you have tried it out on other external hard-drives, and it doesn't work there, that seems to support that workbook corruption could be an issue.

yes , but I've tested it with 4 different files in my system , then I've tested it with external hard drive 2 times more with files that always worked , I guess you guys can download the file, I guess the code will duplicate that template sheet just fine. the same macro that cause me trouble.

Can you get it to work at all right now? If there is not any scenario where you can get it to work right now, workbook corruption is a very real possibility.

sooooo weird, seriously , everything works, only this specific important code stopped working, worked in the past, not any more.

please also walk us through an exact sample for us to try, so that we are sure that we are doing the exact same thing you are when you get the error.
Most importantly, exactly what are you entering into the Input Box?

right now I'm clicking on the button , and type Testing with new name

then I get the error : "path file access error '.\VBC1C8.tmp " (every time different .tmp file shows)
after I click OK I get Runtime error 9 : subscript out of range
then I click DEBUG and "Sheets(strName).Select" in YELLOW.

 

[Joe4]

Hmmm...

I downloaded it and tested it out by clicking the button and entering "Testing" into the Input Box, and it successfully added a sheet called "Testing" to the workbook.
So I don't seem to be able to recreate your error.

Not sure if your Excel somehow became corrupted or what?

 

[netanel99]

Please you guys can someone contribute help with this one ?
I have 2 laptop, both equipped and updated with the same win10 pro + office 19 pro plus ,
and I got the excel file on disk on key, on one laptop it's perfectly works, and on the other I get error on a specifie macro:
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. It used to work just 2 weeks ago

frustrating , I wish there were a solution to this.

Thank you for your help,
Netanel.

 

[Marc L]

​

What means « on disk on key » ?​