Problem with COPY button

[Kenor]

Hi All

I have created a COPY button and a CLEAR button on an Excel file. And I have been using it for 3 months without any problems.

Suddenly the Error below came out when I click COPY button. CLEAR button no problem. I just want to know, is this error related to my VBA code?

I have use below VBA code for this COPY button.

Sub Rectangle2_Click()

Dim ShtName As String

ShtName = Range("Z2").Value & " " & Range("H5").Value & " " & Range("V5").Value
If ShtName = "" Then
MsgBox "no name entered"
Exit Sub
ElseIf Evaluate("isref('" & ShtName & "'!A1)") Then
MsgBox "Sheet name " & ShtName & " is already used"
Exit Sub
End If
ActiveSheet.Copy , Sheets(Sheets.Count)
ActiveSheet.Name = ShtName

End Sub

 

[Colo]

Seems the code you provided us doesn't have any problem. And I've seen the same problem before in other communities like the same kind of error comes out when a worksheet is copied by VBA. Unfortunately, no solutions were posted there. I'd like to know if the file is saved on a local disk or on cloud storage. If so, how does it behave when you make it work locally?

 

[Kenor]

Seems the code you provided us doesn't have any problem. And I've seen the same problem before in other communities like the same kind of error comes out when a worksheet is copied by VBA. Unfortunately, no solutions were posted there. I'd like to know if the file is saved on a local disk or on cloud storage. If so, how does it behave when you make it work locally?

Hi Colo,

I have used the file on my Production PC which uses Microsoft Office 2016 MSO (16.0.4266.1003) 32-bit and stored it in my company's shared folder.

I'm just wondering why this kind of error never happened before and now suddenly appears when clicking the COPY button.

And also, sometimes it works for the first or second time, but after the 3rd time, it becomes an error.

I have tried that file on my laptop which uses Microsoft 365 MSO (Version 2207 Build 16.0.15427.20182) 64-bit and it works without any errors.

 

[Colo]

Hi,
The same cases I saw on the internet also suddenly started having errors same as your case. However, there were cases where the system would operate without error and it was not reproducible. What I noticed in these cases was that in many of the cases where errors were occurring, it appeared that the data was being stored in the cloud. Also in this case of yours, the workbook is saved in a shared folder on the company server. And this error is about not being able to create a temporary file in a local folder. It was a Japanese thread with experts, but in the end, they could not find the cause of the problem. I am wondering if a practical solution would be to make the code not stop when the error comes out but to make it retry to copy the sheet until it succeeds because it seems difficult to move the workbook to a local folder.

Hope other members on this board reply with some information on this matter.