Non-existant Name Causing Name Conflict 1

[ThomasLafferty]

When I attempt to make a copy of a worksheet in my by holding the Ctrl key and dragging and dropping on the sheet tab, I receive a Name Conflict error message. The same error occurs if I try to use VBA to make a copy of the offending sheet in the workbook. Here's the strange part: I have searched both visually with the formulas showing and hidden as well as by using Edit find and looked at formulas, values and comments of every sheet in my book. I have looked at the insert name dialog for every sheet, and I have looked at every line of code using edit find. I can find no reference to the supposed name in any way. Where else should I look? Also, if I click yes to the error, I can probably use the sheet just fine. But here's the catch: I can't suppress the error with my trap, and I will need to otherwise I will see it 90 plus times as it's part of a loop. I need either to find the name and deal with it, or find a way to suppress the error. Here's my code:

Public Sub MoveData()
' Moves data to reporting sheets based on unique manifests
'
'VARIABLE DECLARATIONS
Dim Target As Range, FilterIterations As Integer, SheetsToInsert As Integer
On Error GoTo Err_MoveData


'Create worksheets copies to receive data
    For SheetsToInsert = 0 To FilterIterations - 1
    [!]'Error occurs here[/!]    
    Sheets("TRL").Copy After:=Sheets(4)
        
    Next SheetsToInsert
    
    
Exit_MoveData:
    Exit Sub
    
Err_MoveData:
    Err = 0
    Resume Exit_MoveData

End Sub

Here's a link to a screenshot of the error message, if that helps...

http://www.geocities.com/thomas_e_lafferty/index.html?1152313764417

Help would be appreciated.
Tom

Live once die twice; live twice die once.

 

[firefytr]

Hi Tom,

I've seen this before sometimes too. Named ranges can wreak havoc on worksheets this way. It may have been a named range from another source as well, if copying, etc. What I would do is turn off DisplayAlerts and put a delete line in there to take care of the named range. Something like ..

Public Sub MoveData()
' Moves data to reporting sheets based on unique manifests
'
'VARIABLE DECLARATIONS
    Dim Target As Range, FilterIterations As Long, SheetsToInsert As Long
    Dim ws As Worksheet
    On Error GoTo Err_MoveData
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    'Create worksheets copies to receive data
    On Error Resume Next
    For SheetsToInsert = 0 To FilterIterations - 1
        Sheets("TRL").Copy After:=Sheets(4)
        Sheets(5).Names("wrn.VOYAGE._.CONTRIBUTION._.REPORT.").Delete
    Next SheetsToInsert
    
Exit_MoveData:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub

Err_MoveData:
    Err = 0
    Resume Exit_MoveData

End Sub

I know that the On Error Resume Next statement is basically a blanket, but the next thing I'd do is start stripping out everything from the workbook. Hope it helps.

NB: Untested.

-----------
Regards,
Zack Barresse

 

[ThomasLafferty]

Thanks! Turns out that the original creator of the file had no recollection of that name, so I moved only the values and formats to a new sheet, deleted the offender, renamed it with the original offending sheet's name, ran the code and voila!

Nice idea about deleting the name and turning off the alerts and screenupdating - I like it and will remember it.


Tom

Live once die twice; live twice die once.