| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting of names | Excel Programming | |||
| deleting names from the name box | Excel Discussion (Misc queries) | |||
| deleting duplicate names | Excel Worksheet Functions | |||
| Deleting Names | Excel Programming | |||
| Deleting workbook names w/ VB | Excel Programming | |||
Linear Mode
