names and deleting them
 

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.

names and deleting them
 

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

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 /



On Mar 7, 9:26 pm, Steven Cheng
wrote:
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.

names and deleting them
 

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.

" wrote:

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

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 /



On Mar 7, 9:26 pm, Steven Cheng
wrote:
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.

names and deleting them
 

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 /


On Mar 7, 10:11 pm, Steven Cheng
wrote:
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.

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

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 /

On Mar 7, 9:26 pm, Steven Cheng
wrote:
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.

names and deleting them
 

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?

" wrote:

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

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 /



On Mar 7, 9:26 pm, Steven Cheng
wrote:
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.

names and deleting them
 

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

/ Tyla /

On Mar 7, 10:26 pm, Steven Cheng
wrote:
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?

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

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 /

On Mar 7, 9:26 pm, Steven Cheng
wrote:
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.