Macro Run-Time Error

[usmc_tanks]

Hello,
I'm hoping someone can help me out with an error I'm receiving when I run my macro. I have a macro that will copy the active sheet then paste it to a new worksheet and at the same time it will name the new tab based on the value in cell B3. The problem I'm having is when I run the macro the first time a dialog box pops us saying "File not found" I click the "ok" and then I get the Run-time error '1004': Copy method of worksheet class failed but if I click end and run the macro several more times the macro works perfect, its usually when I open the file the first time.

I know the code works, I just have to go through the motions of clicking end on the run-time error before it starts to work correctly.
Any thoughts?
Thanks!

 

[FormR]

I don't understand why sometimes it works and sometimes it don't?

No, I don't either. So does the message only appear for the one sheet? Can you try manually re-creating the worksheet and deleting the problem one?

Alternatively if you are able to upload the workbook to a file sharing site (like dropbox) and share the link here - someone may be willing to take a look at it for you.

 

[usmc_tanks]

I think you are trying to copy after all the worksheets, are you meaning to add a new sheet then copy to it?

I am trying to copy after all the worksheets. Essentially, I want it to make a copy of what the users input was, move it to a new tab but rename this tab with whats in cell E2.

It's more or less a running log, the user will enter information/answer questions, enter the tag # into cell E2, they click the button and the macro makes a copy, moves it to the end and renames it with what was entered into cell E2. If I ever need to go back and look for a specific tag number, I should be able to find the tab that matches.

I'm not an expert in code, just know enough to be dangerous and confuse myself......

 

[Dryver14]

Is it possible that when naming the new sheet you are naming it the same as another one already in the workbook

 

[usmc_tanks]

No, I don't either. So does the message only appear for the one sheet? Can you try manually re-creating the worksheet and deleting the problem one?

Alternatively if you are able to upload the workbook to a file sharing site (like dropbox) and share the link here - someone may be willing to take a look at it for you.

https://drive.google.com/open?id=1wAByHP8qUIg1NoEU96EtB5NWznSGW8NG

I've added the file at the location above, hopefully the link works. In the file there is a button for "save file" this should move, copy and rename worksheet. It works the first couple times but the error comes up anywhere between 2nd and 4th attempt?

 

[NoSparks]

I added a line to your existing code so I could just keep clicking the button.
I quit after adding about 50 sheets.
No issues at all, just keeps adding and naming them with every click.

Sub Copyrenameworksheet()

    Dim ws As Worksheet
    Set ws = Worksheets(ActiveSheet.Name)
 Application.DisplayAlerts = False
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    Application.DisplayAlerts = True
    If ws.Range("E2").Value <> "" Then
    ActiveSheet.Name = ws.Range("E2").Value
     
    End If
    ws.Activate
    ws.Range("E2").Value = ws.Range("E2").Value + 1
    

End Sub

 

[FormR]

No issues at all, just keeps adding and naming them with every click.

Same here, no issues or messages.

@usmc_tanks - you could try starting Excel in safe mode to disable any add-ins / extensions you have installed and check if the problem still persists?

https://support.office.com/en-gb/ar...ndows-pc-dedf944a-5f4b-4afb-a453-528af4f7ac72

 

[usmc_tanks]

Thanks everyone! I did try safe mode and it worked fine, I do think there is an add-on issue. Possibly the EPM add-on? I just need to keep doing a process of elimination and see which one works. If I can single anything out I'll post it to the forum, again Thanks for looking at the file and providing all your input!
@NoSparks, thanks for the extra line to add the number up +1!

I'll have plenty more questions in the future for macros, I'm trying to simplify a lot of the work my employees are doing by adding the macros to reduce the amount of mistakes by doing it manually.