Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 19th 09, 02:18 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 49
Default Deleting all named ranges that have a workbook scope

Hopefully this is really simple... I've set up a macro-enabled file that
imports excel files from a survey as worksheets - each file becomes a new
sheet. In order to get an overview of the survey answers, each survey file
has a number of named ranges. each with the scope "sheet," so that I can
import fifty files that all have the named range AnswerOne. Unfortunately,
after a recent revision to the survey, it now includes a number of ranges
with the scope "Workbook". If I import one of these files into my collection,
that range becomes global across the entire workbook. When I import the next
file into the collection, its named ranges collide with the existing named
ranges and the entire process breaks down. Therefore I have two questions:

1) Does anybody know of a way to import the files and force all the ranges
in the imported files to be local to the worksheet? That would be ideal,
since I could then have multiple ranges with the same names next to each
other that would only affect their own sheets.

2) How do I change the survey file so that the named ranges are
worksheet-scoped, rather than workbook-scoped? If I try to manage names, the
option to change it from Workbook to Worksheet is grayed out, and I have no
idea why...

Thanks a lot for the help

  #2   Report Post  
Old February 19th 09, 03:11 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 968
Default Deleting all named ranges that have a workbook scope

Try using my Name Manager addin (developed by myself and Jan Karel Pieterse)
download from
http://www.decisionmodels.com/downloads.htm

It has the ability to filter and change names from Global to Local etc.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Babymech" wrote in message
...
Hopefully this is really simple... I've set up a macro-enabled file that
imports excel files from a survey as worksheets - each file becomes a new
sheet. In order to get an overview of the survey answers, each survey file
has a number of named ranges. each with the scope "sheet," so that I can
import fifty files that all have the named range AnswerOne. Unfortunately,
after a recent revision to the survey, it now includes a number of ranges
with the scope "Workbook". If I import one of these files into my
collection,
that range becomes global across the entire workbook. When I import the
next
file into the collection, its named ranges collide with the existing named
ranges and the entire process breaks down. Therefore I have two questions:

1) Does anybody know of a way to import the files and force all the ranges
in the imported files to be local to the worksheet? That would be ideal,
since I could then have multiple ranges with the same names next to each
other that would only affect their own sheets.

2) How do I change the survey file so that the named ranges are
worksheet-scoped, rather than workbook-scoped? If I try to manage names,
the
option to change it from Workbook to Worksheet is grayed out, and I have
no
idea why...

Thanks a lot for the help



  #3   Report Post  
Old February 19th 09, 04:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 49
Default Deleting all named ranges that have a workbook scope

Excellent - thanks. Your Name Manager worked wonders for converting the scope
of the names in the survey, so now I have a version with only local ranges.
However, I still have a number of survey files floating around with the old
setup, ie a bunch of Workbook-ranges... do you know a way to either convert
them by macro, or to use Name Manager to batch change the files I have?

Again - thanks for the tip and the excellent utility.

"Charles Williams" wrote:

Try using my Name Manager addin (developed by myself and Jan Karel Pieterse)
download from
http://www.decisionmodels.com/downloads.htm

It has the ability to filter and change names from Global to Local etc.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Babymech" wrote in message
...
Hopefully this is really simple... I've set up a macro-enabled file that
imports excel files from a survey as worksheets - each file becomes a new
sheet. In order to get an overview of the survey answers, each survey file
has a number of named ranges. each with the scope "sheet," so that I can
import fifty files that all have the named range AnswerOne. Unfortunately,
after a recent revision to the survey, it now includes a number of ranges
with the scope "Workbook". If I import one of these files into my
collection,
that range becomes global across the entire workbook. When I import the
next
file into the collection, its named ranges collide with the existing named
ranges and the entire process breaks down. Therefore I have two questions:

1) Does anybody know of a way to import the files and force all the ranges
in the imported files to be local to the worksheet? That would be ideal,
since I could then have multiple ranges with the same names next to each
other that would only affect their own sheets.

2) How do I change the survey file so that the named ranges are
worksheet-scoped, rather than workbook-scoped? If I try to manage names,
the
option to change it from Workbook to Worksheet is grayed out, and I have
no
idea why...

Thanks a lot for the help




  #4   Report Post  
Old February 19th 09, 04:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 968
Default Deleting all named ranges that have a workbook scope

Don't know of a batch method, what I would do is:
- start Excel
- open all the survey files
- start Name Manager
- set the right Name Manager Filter
- convert the active workbook, save it and close it
- repeat for the next active workbook

Since Name Manager automatically refreshes for the active workbook it should
be quite fast.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Babymech" wrote in message
...
Excellent - thanks. Your Name Manager worked wonders for converting the
scope
of the names in the survey, so now I have a version with only local
ranges.
However, I still have a number of survey files floating around with the
old
setup, ie a bunch of Workbook-ranges... do you know a way to either
convert
them by macro, or to use Name Manager to batch change the files I have?

Again - thanks for the tip and the excellent utility.

"Charles Williams" wrote:

Try using my Name Manager addin (developed by myself and Jan Karel
Pieterse)
download from
http://www.decisionmodels.com/downloads.htm

It has the ability to filter and change names from Global to Local etc.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Babymech" wrote in message
...
Hopefully this is really simple... I've set up a macro-enabled file
that
imports excel files from a survey as worksheets - each file becomes a
new
sheet. In order to get an overview of the survey answers, each survey
file
has a number of named ranges. each with the scope "sheet," so that I
can
import fifty files that all have the named range AnswerOne.
Unfortunately,
after a recent revision to the survey, it now includes a number of
ranges
with the scope "Workbook". If I import one of these files into my
collection,
that range becomes global across the entire workbook. When I import the
next
file into the collection, its named ranges collide with the existing
named
ranges and the entire process breaks down. Therefore I have two
questions:

1) Does anybody know of a way to import the files and force all the
ranges
in the imported files to be local to the worksheet? That would be
ideal,
since I could then have multiple ranges with the same names next to
each
other that would only affect their own sheets.

2) How do I change the survey file so that the named ranges are
worksheet-scoped, rather than workbook-scoped? If I try to manage
names,
the
option to change it from Workbook to Worksheet is grayed out, and I
have
no
idea why...

Thanks a lot for the help









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
deleting multiple named ranges april Excel Discussion (Misc queries) 1 April 15th 08 07:03 PM
Excel {2007} Named Ranges: Renaming, Deleting etc... DemonTraitor Excel Discussion (Misc queries) 4 November 27th 07 12:06 PM
problem in deleting the named ranges Maya[_2_] Excel Worksheet Functions 1 June 28th 07 12:52 PM
Deleting Named Ranges Carl Excel Worksheet Functions 2 August 31st 06 07:16 PM
Deleting many named ranges Tom Hayakawa Excel Worksheet Functions 2 March 28th 05 10:47 PM


All times are GMT +1. The time now is 01:49 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017