Copying Sheets

[ben]

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.

[Tom Ogilvy]

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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in message
news:950B497F-6567-4DDC-9C90-94744A0B107F@microsoft.com...
> 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.

[ben]

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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in message
> news:950B497F-6567-4DDC-9C90-94744A0B107F@microsoft.com...
> > 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.
>
>
>

[ben]

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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in message
> > news:950B497F-6567-4DDC-9C90-94744A0B107F@microsoft.com...
> > > 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.
> >
> >
> >

[Tom Ogilvy]

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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in message
news:D0A11072-905D-43E0-80A1-D48E01BE6B28@microsoft.com...
> 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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in
message
> > news:950B497F-6567-4DDC-9C90-94744A0B107F@microsoft.com...
> > > 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.
> >
> >
> >

[ben]

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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in message
> news:D0A11072-905D-43E0-80A1-D48E01BE6B28@microsoft.com...
> > 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" <bw_rgb@hotmail.com(remove this if mailing direct)> wrote in
> message
> > > news:950B497F-6567-4DDC-9C90-94744A0B107F@microsoft.com...
> > > > 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.
> > >
> > >
> > >
>
>
>