NAMES error 1

[merlynsdad]

I'm modifying a program I wrote about a year ago, which ran perfectly then. Now, at a certain point, it is giving the error "A formula or sheet you want to move or copy contains the name 'NAMES' which already exists in the destination worksheet." I don't know where this is coming from, and there are no range "names" in the destination worksheet, which is the program running the code. The program is moving data from one spreadsheet to another, and the code leading up to the error is as follows:

ws.Activate
ws.Range("$E$6").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=lstText

'Copy the query results to the Query tab in this workbook

ActiveSheet.Cells.Select
Selection.Copy
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
Set rng = ActiveSheet.AutoFilter.Range
Me.Activate
ActiveWorkbook.Sheets("QueryEmp").Select

'The next line will generate an error message; press YES and continue.

rng.Resize(rng.Rows.Count).Copy Destination:=Range("$a$16")

Where is this coming from, and how do I get rid of it? Thanks in advance.

 

[PHV]

What about this ?
rng.Resize(rng.Rows.Count).Copy Destination:=ThisWorkbook.Sheets("QueryEmp").Range("$a$16")

Anyway, why all this Activate and Select stuff ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[merlynsdad]

Didn't make any difference; the error still comes up.

 

[SkipVought]

Hit the DEBUG button and...
faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

If the problem is due to some of the named ranges in your workbook, it might help to list them and check if there are any conflicts.

The following code might be helpful. If you run it it will paste a list of the named ranges starting (top left) at a user-specified cell. The list consists of the range name, it's definition and the current value.

The code is in two parts. The first part does what I've defined above and the second is the code to do the get_user_range_selection which is called by the first code.

Public Sub AL_ListNames()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Description:
'This sub creates a list of all the names and named ranges in th workbook,
'together with a definition of what each range refers to.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Dim sinkrange As Range
Dim reply As Variant
Dim msg As String
Dim r As Integer, c As Integer
Dim calcmethod As Variant

'provide explanation of what will happen & give user chance to quit
msg = "This procedure will list all of the ranges in this workbook, together with their definitions."
msg = msg & Chr(13) & "You will be asked to select a range where you want the list to be written."
msg = msg & Chr(13) & "Select OK to continue or Cancel to quit."

reply = MsgBox(msg, vbOKCancel)
If reply = vbOK Then
    'get the range where the user wants to start writing the results
    Set sinkrange = get_user_range_selection()
    r = 1
    
    'get the names and write them to the spreadsheet
    Dim nm As Name
    On Error Resume Next
    Dim cnt As Long
    calcmethod = Application.Calculation
    Application.Calculation = xlCalculationManual
    sinkrange(r, 1) = "Name"
    sinkrange(r, 2) = "Definition"
    sinkrange(r, 3) = "Value"
    For Each nm In ActiveWorkbook.Names
       r = r + 1
       sinkrange(r, 1) = nm.Name
       sinkrange(r, 2) = "'" & nm.RefersTo
       cnt = nm.RefersToRange.count
       If Err.Number <> 0 Or nm.RefersToRange.count > 1 Then
            sinkrange(r, 3) = "N/A"
            Err.Clear
       Else
            sinkrange(r, 3) = nm.Value
       End If
    Next nm
    Application.Calculation = calcmethod
End If
End Sub

Function get_user_range_selection(Optional usermessage As Variant) As Range
'
' Interrogates user for a range selection and
' returns the selected range
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next

Dim userRange As Range
Dim output As String, msg As String
Dim response As Integer

If Not IsMissing(usermessage) Then
    msg = usermessage
Else
    msg = "Select Cells"
End If

Set userRange = Application.InputBox(Prompt:=msg, Type:=8)
Set get_user_range_selection = userRange

On Error GoTo 0
End Function

Actually, I find the above is a useful little macro anyway. If I remember rightly, it was derived from info picked up on this forum.

Tony

 

[merlynsdad]

Thanks! I'm finding all sorts of junk in there!

 

[N1GHTEYES]

Did you find any range called "names"?

 

[merlynsdad]

Actually I found 3: NAMES, NAMES1 AND NAMES2. The definition in all 3 cases is the range NAMES in the underlying workbook. There are multiple references to that workbook in this workbook; would those cause the duplicates?

 

[N1GHTEYES]

I think it sounds like you are copying a range which includes cells which refer to the "names" range into another workbook.

Excel is asking you if you want the cells to continue to refer to that original workbook, or to refer to a different "names" range in the destination. How you answer that question will depend on how your workbook is organised and what you are trying to do.

Depending on how complex the workbook is, you might find it easier to simply refer to the specific range in the cell formulae rather than a named range. Of course, you'll still have to decide if you want to continue to refer to the original range or the range in the equivalent position in the new book.

You MIGHT be able to stop it from displaying the message by setting Application.Displayalerts to false before the code step which is causing the problem, but I doubt it. I think you will need to sort out the logic of which cells actually need to be refered to by the formulae in the destination workbook.