macro to remove a named range always called "database" & if possib

I need a macro to remove a named range in a worksheet always called
"database" also it would be great to add to this functionality the ability to
save the sheet as it is named in the most current excel file format.
Currently it is saving out of a report writer as Excel version 3.

I would fire it off while the workbook is open and active - there may be
other work books open that I would want to be left alone.


Getting back into macro's after awhile away so rusty. I assume I would put
this marco in personal to be accessable to the many sheets like this I deal
with a day. But if you prefer to stick it elsewhere no problem.

Thanks Todd Frisch

sub Cleanup()
With ActiveWorkbook
.Names("Database").RefersToRange.ClearContents
.Names("Database").Delete
Application.DisplayAlerts = False
.SaveAs Thisworkbook.FullName, xlWorkbook.Normal
Application.DisplayAlerts = True
End With
End sub

Saying you want to remove a named range is a bit ambiguous. You can modify
the above to suit you needs.

--
Regards,
Tom Ogilvy


"Todd F." <ToddF@discussions.microsoft.com> wrote in message
news:B1EE6603-6EC3-41C1-B62A-B3736A0F3953@microsoft.com...
> I need a macro to remove a named range in a worksheet always called
> "database" also it would be great to add to this functionality the ability
to
> save the sheet as it is named in the most current excel file format.
> Currently it is saving out of a report writer as Excel version 3.
>
> I would fire it off while the workbook is open and active - there may be
> other work books open that I would want to be left alone.
>
>
> Getting back into macro's after awhile away so rusty. I assume I would put
> this marco in personal to be accessable to the many sheets like this I
deal
> with a day. But if you prefer to stick it elsewhere no problem.
>
> Thanks Todd Frisch
>
>

Thanks Tom I di get an error when executee dthe macro - it said "invalid
qualifier" and it was at "xlWorkbook" .normal

I did read the help button but did not follow it.

apprecaite the time. Todd

"Tom Ogilvy" wrote:

> sub Cleanup()
> With ActiveWorkbook
> .Names("Database").RefersToRange.ClearContents
> .Names("Database").Delete
> Application.DisplayAlerts = False
> .SaveAs Thisworkbook.FullName, xlWorkbook.Normal
> Application.DisplayAlerts = True
> End With
> End sub
>
> Saying you want to remove a named range is a bit ambiguous. You can modify
> the above to suit you needs.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Todd F." <ToddF@discussions.microsoft.com> wrote in message
> news:B1EE6603-6EC3-41C1-B62A-B3736A0F3953@microsoft.com...
> > I need a macro to remove a named range in a worksheet always called
> > "database" also it would be great to add to this functionality the ability
> to
> > save the sheet as it is named in the most current excel file format.
> > Currently it is saving out of a report writer as Excel version 3.
> >
> > I would fire it off while the workbook is open and active - there may be
> > other work books open that I would want to be left alone.
> >
> >
> > Getting back into macro's after awhile away so rusty. I assume I would put
> > this marco in personal to be accessable to the many sheets like this I
> deal
> > with a day. But if you prefer to stick it elsewhere no problem.
> >
> > Thanks Todd Frisch
> >
> >
>
>
>

Must have been a typo. There should be no period in the constant
xlworkbooknormal

.SaveAs Thisworkbook.FullName, xlWorkbookNormal

--
Regards,
Tom Ogilvy

"Todd F." <ToddF@discussions.microsoft.com> wrote in message
news:77A8D15F-E87A-4738-8412-FC76949656D6@microsoft.com...
> Thanks Tom I di get an error when executee dthe macro - it said "invalid
> qualifier" and it was at "xlWorkbook" .normal
>
> I did read the help button but did not follow it.
>
> apprecaite the time. Todd
>
> "Tom Ogilvy" wrote:
>
> > sub Cleanup()
> > With ActiveWorkbook
> > .Names("Database").RefersToRange.ClearContents
> > .Names("Database").Delete
> > Application.DisplayAlerts = False
> > .SaveAs Thisworkbook.FullName, xlWorkbook.Normal
> > Application.DisplayAlerts = True
> > End With
> > End sub
> >
> > Saying you want to remove a named range is a bit ambiguous. You can
modify
> > the above to suit you needs.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Todd F." <ToddF@discussions.microsoft.com> wrote in message
> > news:B1EE6603-6EC3-41C1-B62A-B3736A0F3953@microsoft.com...
> > > I need a macro to remove a named range in a worksheet always called
> > > "database" also it would be great to add to this functionality the
ability
> > to
> > > save the sheet as it is named in the most current excel file format.
> > > Currently it is saving out of a report writer as Excel version 3.
> > >
> > > I would fire it off while the workbook is open and active - there may
be
> > > other work books open that I would want to be left alone.
> > >
> > >
> > > Getting back into macro's after awhile away so rusty. I assume I would
put
> > > this marco in personal to be accessable to the many sheets like this I
> > deal
> > > with a day. But if you prefer to stick it elsewhere no problem.
> > >
> > > Thanks Todd Frisch
> > >
> > >
> >
> >
> >

..SaveAs ThisWorkbook.FullName, xlWorkbookNormal

when I run the macro the above line of you rcode is highlighted yellow and I
get message

"run time error 1004 you cannot ssave this workbook with the same name as
another open workbook or add-in. Choose a different name , or cloase the
other workbook or add-in before saving.

Also all data on sheet disappears and is gone even when reopening.

I am naming the sheet as it dumps from report writer and then opening and
saving as manually the same sheet name - my goal with this macro is to do the
same "save as" same name.

Thanks

"Tom Ogilvy" wrote:

> Must have been a typo. There should be no period in the constant
> xlworkbooknormal
>
> .SaveAs Thisworkbook.FullName, xlWorkbookNormal
>
> --
> Regards,
> Tom Ogilvy
>
> "Todd F." <ToddF@discussions.microsoft.com> wrote in message
> news:77A8D15F-E87A-4738-8412-FC76949656D6@microsoft.com...
> > Thanks Tom I di get an error when executee dthe macro - it said "invalid
> > qualifier" and it was at "xlWorkbook" .normal
> >
> > I did read the help button but did not follow it.
> >
> > apprecaite the time. Todd
> >
> > "Tom Ogilvy" wrote:
> >
> > > sub Cleanup()
> > > With ActiveWorkbook
> > > .Names("Database").RefersToRange.ClearContents
> > > .Names("Database").Delete
> > > Application.DisplayAlerts = False
> > > .SaveAs Thisworkbook.FullName, xlWorkbook.Normal
> > > Application.DisplayAlerts = True
> > > End With
> > > End sub
> > >
> > > Saying you want to remove a named range is a bit ambiguous. You can
> modify
> > > the above to suit you needs.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Todd F." <ToddF@discussions.microsoft.com> wrote in message
> > > news:B1EE6603-6EC3-41C1-B62A-B3736A0F3953@microsoft.com...
> > > > I need a macro to remove a named range in a worksheet always called
> > > > "database" also it would be great to add to this functionality the
> ability
> > > to
> > > > save the sheet as it is named in the most current excel file format.
> > > > Currently it is saving out of a report writer as Excel version 3.
> > > >
> > > > I would fire it off while the workbook is open and active - there may
> be
> > > > other work books open that I would want to be left alone.
> > > >
> > > >
> > > > Getting back into macro's after awhile away so rusty. I assume I would
> put
> > > > this marco in personal to be accessable to the many sheets like this I
> > > deal
> > > > with a day. But if you prefer to stick it elsewhere no problem.
> > > >
> > > > Thanks Todd Frisch
> > > >
> > > >
> > >
> > >
> > >
>
>
>

I must have been asleep at the wheel this morning. Thisworkbook should be
activeworkbook.

With ActiveWorkbook
.Names("Database").RefersToRange.ClearContents
.Names("Database").Delete
Application.DisplayAlerts = False
.SaveAs ActiveWorkbook.FullName, xlWorkbook.Normal
Application.DisplayAlerts = True
End With
End sub

sorry for the confusion.

--
Regards,
Tom Ogilvy


"Todd F." <ToddF@discussions.microsoft.com> wrote in message
news:518B5C86-D3DF-43CE-B700-A4102FE5684A@microsoft.com...
> .SaveAs ThisWorkbook.FullName, xlWorkbookNormal
>
> when I run the macro the above line of you rcode is highlighted yellow and
I
> get message
>
> "run time error 1004 you cannot ssave this workbook with the same name as
> another open workbook or add-in. Choose a different name , or cloase the
> other workbook or add-in before saving.
>
> Also all data on sheet disappears and is gone even when reopening.
>
> I am naming the sheet as it dumps from report writer and then opening and
> saving as manually the same sheet name - my goal with this macro is to do
the
> same "save as" same name.
>
> Thanks
>
> "Tom Ogilvy" wrote:
>
> > Must have been a typo. There should be no period in the constant
> > xlworkbooknormal
> >
> > .SaveAs Thisworkbook.FullName, xlWorkbookNormal
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Todd F." <ToddF@discussions.microsoft.com> wrote in message
> > news:77A8D15F-E87A-4738-8412-FC76949656D6@microsoft.com...
> > > Thanks Tom I di get an error when executee dthe macro - it said
"invalid
> > > qualifier" and it was at "xlWorkbook" .normal
> > >
> > > I did read the help button but did not follow it.
> > >
> > > apprecaite the time. Todd
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > sub Cleanup()
> > > > With ActiveWorkbook
> > > > .Names("Database").RefersToRange.ClearContents
> > > > .Names("Database").Delete
> > > > Application.DisplayAlerts = False
> > > > .SaveAs Thisworkbook.FullName, xlWorkbook.Normal
> > > > Application.DisplayAlerts = True
> > > > End With
> > > > End sub
> > > >
> > > > Saying you want to remove a named range is a bit ambiguous. You can
> > modify
> > > > the above to suit you needs.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Todd F." <ToddF@discussions.microsoft.com> wrote in message
> > > > news:B1EE6603-6EC3-41C1-B62A-B3736A0F3953@microsoft.com...
> > > > > I need a macro to remove a named range in a worksheet always
called
> > > > > "database" also it would be great to add to this functionality the
> > ability
> > > > to
> > > > > save the sheet as it is named in the most current excel file
format.
> > > > > Currently it is saving out of a report writer as Excel version 3.
> > > > >
> > > > > I would fire it off while the workbook is open and active - there
may
> > be
> > > > > other work books open that I would want to be left alone.
> > > > >
> > > > >
> > > > > Getting back into macro's after awhile away so rusty. I assume I
would
> > put
> > > > > this marco in personal to be accessable to the many sheets like
this I
> > > > deal
> > > > > with a day. But if you prefer to stick it elsewhere no problem.
> > > > >
> > > > > Thanks Todd Frisch
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

the file format when you open the file in excel says "excel 3.0 worksheet" I
think I said 3.0 workbook. Sorry for confussion if this is critical.

"Tom Ogilvy" wrote:

> sub Cleanup()
> With ActiveWorkbook
> .Names("Database").RefersToRange.ClearContents
> .Names("Database").Delete
> Application.DisplayAlerts = False
> .SaveAs Thisworkbook.FullName, xlWorkbook.Normal
> Application.DisplayAlerts = True
> End With
> End sub
>
> Saying you want to remove a named range is a bit ambiguous. You can modify
> the above to suit you needs.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Todd F." <ToddF@discussions.microsoft.com> wrote in message
> news:B1EE6603-6EC3-41C1-B62A-B3736A0F3953@microsoft.com...
> > I need a macro to remove a named range in a worksheet always called
> > "database" also it would be great to add to this functionality the ability
> to
> > save the sheet as it is named in the most current excel file format.
> > Currently it is saving out of a report writer as Excel version 3.
> >
> > I would fire it off while the workbook is open and active - there may be
> > other work books open that I would want to be left alone.
> >
> >
> > Getting back into macro's after awhile away so rusty. I assume I would put
> > this marco in personal to be accessable to the many sheets like this I
> deal
> > with a day. But if you prefer to stick it elsewhere no problem.
> >
> > Thanks Todd Frisch
> >
> >
>
>
>