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
|