names and deleting them

[Guest]

I am running a code that is trying to clear out the names that are being
copied over from one workbook to another....

i tried to do this...

Private sub whateveritiscalled()

dim n as name
dim wkb as workbook

set wkb = workbooks.add
thisworkworkbook.activesheet.cells.copy
wkb.activesheet.range("a1").paste 'actually it is pastespecial...not important

for each n in wkb.names
n.delete

next

etc...etc...etc..

however, it keeps getting an error on the n.delete statement...run time
error 1004...must be missing something....can anyone help.

 

[tylagusim]

Are you sure the error is at the 'n.delete'? The following code works
here:

Sub whatever()

Dim n As Name
Dim wkb As Workbook

Set wkb = Workbooks.Add
ThisWorkbook.ActiveSheet.Cells.Copy

wkb.Activate
ActiveSheet.Paste
For Each n In wkb.Names
n.Delete
Next

Application.CutCopyMode = False

End Sub

/ Tyla /

 

[Guest]

yes, I was walking through the code and noticed that the n variable revealed
an error "NAME#"...the only thing that I can see is that in your code you
activated wkb first.

I'll give that a shot. but thanks for responding in any case.

 

[tylagusim]

What version of Excel are you using? In Excel 2003, the statement:
wkb.ActiveSheet.Range("a1").Paste
is invalid. My code simply used a different syntax.

If you take out everything but the "For" loop in your original code,
does it work?

/ Tyla /

 

[Guest]

Okay found it. I am using XL2007 and there appears to be a hidden name
called "_xlfn.SUMIFS" and yes my file uses SUMIFS function.

what is this name for?

 

[tylagusim]

Beats me! I'll have to play with it under XL12 -- probably tomorrow.
Hopefully wiser heads than ours can answer.

/ Tyla /