Problems copying a worksheet

**Ascopeland** · 12-28-2012, 03:02 PM

I am using the code below to copy a hidden worksheet (the name of the existing worksheet is being passed to the subroutine), and places it at the far right of the other sheets. It then renames a cell and the sheet name. It also makes it visible and makes it the active sheet.

When the WrkSht.Copy line is executed, I get a pop-up notification window that says, "A sheet you want to move or copy contains the name 'RecipeName', which already exists in the destination worksheet. Do you want to use this version of the name? blah blahblah..."

How do I fix it so this does not pop up anymore? What do I need to do with 'RecipeName' to fix this?

Here is the code:

Please Login or Register  to view this content.

Moderators Note: Please follow Forum Rule #3 and use code tags. Added this time, but please use them in the future…Thanks.

**AB33** · 12-28-2012, 03:12 PM

Put these lines on the top and bottom of your code, but the down side is if you macro can only work if you press yes, or no, these alerts will pass by the code.

Please Login or Register  to view this content.

**OllieB** · 12-28-2012, 03:18 PM

I have run the code a number of times and I do not get the error mentioned unless I do not provide a new name twice in a row in which case the worksheet "New Recipe" already exists in the workbook and cannot be added again I.e. the rename statement fails and not the copy statement). Perhaps the "default" value assigned to the InputBox result is causing the problem?

**Ascopeland** · 12-28-2012, 03:27 PM

Nice work-around!!

Thank you!

**Ascopeland** · 12-28-2012, 03:34 PM

Thanks Olaf. I will look into and let you know what I find out.

**Ascopeland** · 12-29-2012, 12:42 PM

Olaf, I stepped through the code, and the problem happens when the copy line is executed. Apparently, it doesn't like the fact that 'RecipeName' is used in the original and is also going to be used in the new sheet that was copied. Here is the code again so people don't have to scroll up to see it:

Sub CopySheet(WorkSheetName As String)

'Application.ScreenUpdating = 0
'Application.DisplayAlerts = 0
'Application.EnableEvents = 0

Dim RecipeName As String

RecipeName = InputBox(Prompt:="Enter name of recipe here. You can edit the name later as well.", _
Title:="RECIPE NAME", Default:="New Recipe")

Worksheets(WorkSheetName).Copy After:=Worksheets(Worksheets.Count) 'debug this
Worksheets("Recipe Template (2)").Range("a1").Value = RecipeName
Worksheets("Recipe Template (2)").Name = RecipeName
Worksheets(RecipeName).Visible = True
Worksheets(RecipeName).Activate

'Application.ScreenUpdating = 1
'Application.DisplayAlerts = 1
'Application.EnableEvents = 1

End Sub

**OllieB** · 12-29-2012, 02:01 PM

Ok, like I said it does not fail on the copy line on my PC. One more idea. Please try

Please Login or Register  to view this content.

**Ascopeland** · 12-29-2012, 07:03 PM

It was a good suggestion...but it still happens

I'll keep digging and post whatever I can find out.

**Ascopeland** · 12-29-2012, 07:16 PM

I found the solution here:
http://answers.microsoft.com/en-us/o...6-0183480d376f

I had to go into the name manager as the link says to do and delete all instances of 'RecipeName' that was being used! Everything works great now! thanks for all your help.