Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
combining values and text to make a reference for "named range" | Excel Worksheet Functions | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
Modify "pick from list" to read named range | Excel Programming | |||
Adding named range gives error "method range of object _Global failed " | Excel Programming |