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

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

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." wrote in message
...
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

Invalid qualafier @ "xlWorkbook.Normal
 

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." wrote in message
...
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

clarify file format
 

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." wrote in message
...
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

Invalid qualafier @ "xlWorkbook.Normal
 

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

.SaveAs Thisworkbook.FullName, xlWorkbookNormal

--
Regards,
Tom Ogilvy

"Todd F." wrote in message
...
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." wrote in message
...
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

runtime error 1004 and nukes sheet
 

..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." wrote in message
...
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." wrote in message
...
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

runtime error 1004 and nukes sheet
 

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." wrote in message
...
.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." wrote in message
...
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." wrote in message
...
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

runs after commenting out a line - please confirm
 

Could you confirm this is good to go and hey thanks for your time as always

The below macro is the result of your efforts with a modification - I
commented out a line that was clearing the contents of the anmed range
database.

I just wanted to kill the named range "database and leave the cell contents
intact because the named range was screwing with me.

Sub Cleanup()
With ActiveWorkbook
'.Names("Database").RefersToRange.ClearContents ( taf commented out)
.Names("Database").Delete
Application.DisplayAlerts = False
.SaveAs ActiveWorkbook.FullName, xlWorkbookNormal
Application.DisplayAlerts = True
End With
End Sub

"Tom Ogilvy" wrote:

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." wrote in message
...
.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." wrote in message
...
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." wrote in message
...
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

runs after commenting out a line - please confirm
 

That should work. You could also eliminate the second Activeworkbook within
the With statement as shown.

Sub Cleanup()
With ActiveWorkbook
.Names("Database").Delete
Application.DisplayAlerts = False
.SaveAs .FullName, xlWorkbookNormal
Application.DisplayAlerts = True
End With
End Sub

--
Regards,
Tom Ogilvy


"Todd F." wrote in message
...
Could you confirm this is good to go and hey thanks for your time as
always

The below macro is the result of your efforts with a modification - I
commented out a line that was clearing the contents of the anmed range
database.

I just wanted to kill the named range "database and leave the cell
contents
intact because the named range was screwing with me.

Sub Cleanup()
With ActiveWorkbook
'.Names("Database").RefersToRange.ClearContents ( taf commented
out)
.Names("Database").Delete
Application.DisplayAlerts = False
.SaveAs ActiveWorkbook.FullName, xlWorkbookNormal
Application.DisplayAlerts = True
End With
End Sub

"Tom Ogilvy" wrote:

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." wrote in message
...
.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." wrote in message
...
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." wrote in message
...
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

runs after commenting out a line - please confirm
 

thanks again

"Tom Ogilvy" wrote:

That should work. You could also eliminate the second Activeworkbook within
the With statement as shown.

Sub Cleanup()
With ActiveWorkbook
.Names("Database").Delete
Application.DisplayAlerts = False
.SaveAs .FullName, xlWorkbookNormal
Application.DisplayAlerts = True
End With
End Sub

--
Regards,
Tom Ogilvy


"Todd F." wrote in message
...
Could you confirm this is good to go and hey thanks for your time as
always

The below macro is the result of your efforts with a modification - I
commented out a line that was clearing the contents of the anmed range
database.

I just wanted to kill the named range "database and leave the cell
contents
intact because the named range was screwing with me.

Sub Cleanup()
With ActiveWorkbook
'.Names("Database").RefersToRange.ClearContents ( taf commented
out)
.Names("Database").Delete
Application.DisplayAlerts = False
.SaveAs ActiveWorkbook.FullName, xlWorkbookNormal
Application.DisplayAlerts = True
End With
End Sub

"Tom Ogilvy" wrote:

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." wrote in message
...
.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." wrote in message
...
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." wrote in message
...
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