Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Sheets
I have a workbook with a sub to copy a specific Worksheet and paste a copy to
the end of the workbook. The workbook contains defined names. The worksheet to copy DOES NOT. When I copy this worksheet, I keep getting the error message that the worksheet you are trying to copy contains a Range or Formula with the name (three different names I keep getting ("shoes","vc","bags"), I did a search in my defined names list they do not exist, I did i search in my formulas and the search text was not found, I even went into my macros and checked the sheet modules that were being copied, but the text was not even found there. Anybody know why this might be happening. NOTE: This only started happening when i made the sheet modules Option Explicit ben -- When you lose your mind, you free your life. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Sheets
try running this code
sub ShowNames() On error goto 0 msgbox "Shoes: " & Activeworkbook.Names("shoes").RefersTo msgbox "vc: " & activeworkbook.Names("vc").Refersto msgbox "bags: " & activeworkbook.Names("bags").Refersto On Error goto 0 End Sub -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... I have a workbook with a sub to copy a specific Worksheet and paste a copy to the end of the workbook. The workbook contains defined names. The worksheet to copy DOES NOT. When I copy this worksheet, I keep getting the error message that the worksheet you are trying to copy contains a Range or Formula with the name (three different names I keep getting ("shoes","vc","bags"), I did a search in my defined names list they do not exist, I did i search in my formulas and the search text was not found, I even went into my macros and checked the sheet modules that were being copied, but the text was not even found there. Anybody know why this might be happening. NOTE: This only started happening when i made the sheet modules Option Explicit ben -- When you lose your mind, you free your life. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Sheets
Tom,
Ran the code the results as follows: Shoes - Sheet1$a$1:R0C0 VC - Sheet1$ew$757:R0C0 bags - Sheet1$a$1:R0C0 my interpritation of that is that they really don't exist, but of course i could be easily wrong on that??? "Tom Ogilvy" wrote: try running this code sub ShowNames() On error goto 0 msgbox "Shoes: " & Activeworkbook.Names("shoes").RefersTo msgbox "vc: " & activeworkbook.Names("vc").Refersto msgbox "bags: " & activeworkbook.Names("bags").Refersto On Error goto 0 End Sub -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... I have a workbook with a sub to copy a specific Worksheet and paste a copy to the end of the workbook. The workbook contains defined names. The worksheet to copy DOES NOT. When I copy this worksheet, I keep getting the error message that the worksheet you are trying to copy contains a Range or Formula with the name (three different names I keep getting ("shoes","vc","bags"), I did a search in my defined names list they do not exist, I did i search in my formulas and the search text was not found, I even went into my macros and checked the sheet modules that were being copied, but the text was not even found there. Anybody know why this might be happening. NOTE: This only started happening when i made the sheet modules Option Explicit ben -- When you lose your mind, you free your life. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Sheets
of course I was wrong I just forced delete of the names through code (since I
don't need them) and everything is peachy now. Thanks Tom ben "ben" wrote: Tom, Ran the code the results as follows: Shoes - Sheet1$a$1:R0C0 VC - Sheet1$ew$757:R0C0 bags - Sheet1$a$1:R0C0 my interpritation of that is that they really don't exist, but of course i could be easily wrong on that??? "Tom Ogilvy" wrote: try running this code sub ShowNames() On error goto 0 msgbox "Shoes: " & Activeworkbook.Names("shoes").RefersTo msgbox "vc: " & activeworkbook.Names("vc").Refersto msgbox "bags: " & activeworkbook.Names("bags").Refersto On Error goto 0 End Sub -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... I have a workbook with a sub to copy a specific Worksheet and paste a copy to the end of the workbook. The workbook contains defined names. The worksheet to copy DOES NOT. When I copy this worksheet, I keep getting the error message that the worksheet you are trying to copy contains a Range or Formula with the name (three different names I keep getting ("shoes","vc","bags"), I did a search in my defined names list they do not exist, I did i search in my formulas and the search text was not found, I even went into my macros and checked the sheet modules that were being copied, but the text was not even found there. Anybody know why this might be happening. NOTE: This only started happening when i made the sheet modules Option Explicit ben -- When you lose your mind, you free your life. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Sheets
The definitely exists
Run this sub MakeNamesVisible() On error goto 0 Activeworkbook.Names("shoes").Visible = True Activeworkbook.Names("vc").Visible = True Activeworkbook.Names("bags").Visible = True On Error goto 0 End Sub Then you should be able to see them in Insert=Names=Define sub MakeNamesGone() On error goto 0 Activeworkbook.Names("shoes").Delete Activeworkbook.Names("vc").Delete Activeworkbook.Names("bags").Delete On Error goto 0 End Sub May work to get rid of them if that is what you want. Sometimes names can be resistant to being deleted. -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... Tom, Ran the code the results as follows: Shoes - Sheet1$a$1:R0C0 VC - Sheet1$ew$757:R0C0 bags - Sheet1$a$1:R0C0 my interpritation of that is that they really don't exist, but of course i could be easily wrong on that??? "Tom Ogilvy" wrote: try running this code sub ShowNames() On error goto 0 msgbox "Shoes: " & Activeworkbook.Names("shoes").RefersTo msgbox "vc: " & activeworkbook.Names("vc").Refersto msgbox "bags: " & activeworkbook.Names("bags").Refersto On Error goto 0 End Sub -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... I have a workbook with a sub to copy a specific Worksheet and paste a copy to the end of the workbook. The workbook contains defined names. The worksheet to copy DOES NOT. When I copy this worksheet, I keep getting the error message that the worksheet you are trying to copy contains a Range or Formula with the name (three different names I keep getting ("shoes","vc","bags"), I did a search in my defined names list they do not exist, I did i search in my formulas and the search text was not found, I even went into my macros and checked the sheet modules that were being copied, but the text was not even found there. Anybody know why this might be happening. NOTE: This only started happening when i made the sheet modules Option Explicit ben -- When you lose your mind, you free your life. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Sheets
that's interesting I was not aware names could be invisible.
"Tom Ogilvy" wrote: The definitely exists Run this sub MakeNamesVisible() On error goto 0 Activeworkbook.Names("shoes").Visible = True Activeworkbook.Names("vc").Visible = True Activeworkbook.Names("bags").Visible = True On Error goto 0 End Sub Then you should be able to see them in Insert=Names=Define sub MakeNamesGone() On error goto 0 Activeworkbook.Names("shoes").Delete Activeworkbook.Names("vc").Delete Activeworkbook.Names("bags").Delete On Error goto 0 End Sub May work to get rid of them if that is what you want. Sometimes names can be resistant to being deleted. -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... Tom, Ran the code the results as follows: Shoes - Sheet1$a$1:R0C0 VC - Sheet1$ew$757:R0C0 bags - Sheet1$a$1:R0C0 my interpritation of that is that they really don't exist, but of course i could be easily wrong on that??? "Tom Ogilvy" wrote: try running this code sub ShowNames() On error goto 0 msgbox "Shoes: " & Activeworkbook.Names("shoes").RefersTo msgbox "vc: " & activeworkbook.Names("vc").Refersto msgbox "bags: " & activeworkbook.Names("bags").Refersto On Error goto 0 End Sub -- Regards, Tom Ogilvy "ben" (remove this if mailing direct) wrote in message ... I have a workbook with a sub to copy a specific Worksheet and paste a copy to the end of the workbook. The workbook contains defined names. The worksheet to copy DOES NOT. When I copy this worksheet, I keep getting the error message that the worksheet you are trying to copy contains a Range or Formula with the name (three different names I keep getting ("shoes","vc","bags"), I did a search in my defined names list they do not exist, I did i search in my formulas and the search text was not found, I even went into my macros and checked the sheet modules that were being copied, but the text was not even found there. Anybody know why this might be happening. NOTE: This only started happening when i made the sheet modules Option Explicit ben -- When you lose your mind, you free your life. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Sheets | Excel Discussion (Misc queries) | |||
copying sheets | Excel Discussion (Misc queries) | |||
Copying from other sheets | Excel Programming | |||
Copying sheets | Excel Programming | |||
Copying Sheets | Excel Programming |