Problem with COPY button

Kenor

Board Regular
Joined
Dec 8, 2020
Messages
116
Office Version
  1. 2016
Platform
  1. Windows
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.

1661481024479.png


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?

1661480332353.png
1661481440801.png


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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top