one VBA macro used to work. Suddenly Gives error

netanel99

Board Regular
Joined
May 13, 2013
Messages
168
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
VBA Code:
 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

Well-known Member
Joined
Apr 5, 2021
Messages
1,146
Office Version
  1. 2010
Platform
  1. Windows
Just obviously check the variables contents according to the existing …​
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 13, 2013
Messages
168
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,146
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

♪ Step-By-Step ! ♫​
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
168
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,372
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
May 13, 2013
Messages
168
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,146
Office Version
  1. 2010
Platform
  1. Windows
What means « on disk on key » ?​
 

Forum statistics

Threads
1,136,845
Messages
5,678,101
Members
419,742
Latest member
Dropzyl88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top