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]

Hi, welcome to the forum!

I think for anyone to stand a chance of helping you'll need to show us your code and let us know specifically which line is generating the error.

 

[usmc_tanks]

Hi, welcome to the forum!

I think for anyone to stand a chance of helping you'll need to show us your code and let us know specifically which line is generating the error.

Thanks MVP!

When I do run the debug it's highlighting ActiveSheet.Copy After:=Worksheets(Sheets.Count)

Sub Copyrenameworksheet()
Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("B3").Value <> "" Then
ActiveSheet.Name = wh.Range("B3").Value

Call DataToMasterSheet

End If
wh.Activate
End Sub

 

[FormR]

Hi, I can't see anything obvious that would cause that error message. What happens if you attempt copy the active sheet manually through the UI?

Note, you have declared a variable called "ws" and then go on to to use "wh" in the code.

 

[usmc_tanks]

Hi, I can't see anything obvious that would cause that error message. What happens if you attempt copy the active sheet manually through the UI?

Note, you have declared a variable called "ws" and then go on to to use "wh" in the code.

Thanks!

I just tried to manually do it and I'm getting a File not found pop up "\AppData\Temp\VBB1CB.tmp"

Thanks for the note, I didn't realize it.....

 

[FormR]

Do you have a formula (or something else) on the sheet that somehow points to that file? Personally I'd try to track it down and remove it (assuming it's not needed) if you do but you may be able to suppress the message by turning off application alerts with.

Application.DisplayAlerts = False
'Your code to copy the sheet..
Application.DisplayAlerts = True

 

[usmc_tanks]

Thanks for the help! I have other macros in the sheet but nothing that points to a file location. I did add the code you mentioned it worked the first run but on the second I get the same error message "File not found:" "C:\Users\myname\AppData\Local\Temp\VBC0D5.tmp" and the same run time error.

I'm at a loss, I've looked in the temp files and the excel file itself and find nothing...

 

[Dryver14]

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

 

[FormR]

I get the same error message "File not found:" "C:\Users\myname\AppData\Local\Temp\VBC0D5.tmp" and the same run time error.

Do you get the same message if you try to copy other sheets in the workbook?
Do you get any hits if you do CTRL+F and search the sheet for that file name?

 

[usmc_tanks]

At first before I ran the macro I could move/copy either sheet. I tried again after running the macro and the same file not found message popped up.

I opened the file this morning and ran through the macro and it ran fine, on the fourth run the error message came back. When I debug it always highlights the
row: ActiveSheet.Copy After: = Worksheets (Sheets.Count) . I did add your suggestion from the earlier post.

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

I did do a search and found nothing. I don't understand why sometimes it works and sometimes it don't?