Hi guys, I've looked around at CPearson, OZGrid and Debra's (the name of
which escapes me atm) but I haven't so far found a solution to my problem.

I'm pulling in data from another workbook and then defining named ranges
based on the updated data. This is all working fine as are the lines that
work out the name of the range and the address of the range. Here's the code
I'm using...

dynRangeName = Cells(thisRow, thisCol).Value & depts(thisCol) & "Testers"
Set dynamicRange = Range(Cells(startRow, thisCol + 1), Cells(thisRow,
thisCol + 1))
Debug.Print dynRangeName & " = " & dynamicRange.Address
ThisWorkbook.Names.Add dynRangeName, dynamicRange

The Debug.Print line outputs lines such as these (which are the correct
names and the correct ranges):

ESFncTesters = $B$2:$B$61
LSFncTesters = $B$62:$B$123
NSFncTesters = $B$124:$B$174

My only problem now is that these named ranges that I've added using
"ThisWorkbook.Names.Add" aren't displaying in the Names list (through Insert
Names Define...) and as I'm using these NRs for Data Validation, the DV
doesn't work :-(

Any ideas? I've tried using "Visible:True" on the creation of the names with
no luck. If I create all these names with "rubbish" ranges through the menu
system in Excel when VBA overwrites these names it works, but I didn't really
want to go down this route if I can help it...I don't want to have to rely on
me (or other users) inserting names so the VBA can add them correctly...

Thanks as usual - this forum's been a superb resource for me. Top work you
guys!
George

check in file properties contents tab the named ranges i create with code
are usually there.

"George" wrote:

Hi guys, I've looked around at CPearson, OZGrid and Debra's (the name of
which escapes me atm) but I haven't so far found a solution to my problem.

I'm pulling in data from another workbook and then defining named ranges
based on the updated data. This is all working fine as are the lines that
work out the name of the range and the address of the range. Here's the code
I'm using...

dynRangeName = Cells(thisRow, thisCol).Value & depts(thisCol) & "Testers"
Set dynamicRange = Range(Cells(startRow, thisCol + 1), Cells(thisRow,
thisCol + 1))
Debug.Print dynRangeName & " = " & dynamicRange.Address
ThisWorkbook.Names.Add dynRangeName, dynamicRange

The Debug.Print line outputs lines such as these (which are the correct
names and the correct ranges):

ESFncTesters = $B$2:$B$61
LSFncTesters = $B$62:$B$123
NSFncTesters = $B$124:$B$174

My only problem now is that these named ranges that I've added using
"ThisWorkbook.Names.Add" aren't displaying in the Names list (through Insert
Names Define...) and as I'm using these NRs for Data Validation, the DV
doesn't work :-(

Any ideas? I've tried using "Visible:True" on the creation of the names with
no luck. If I create all these names with "rubbish" ranges through the menu
system in Excel when VBA overwrites these names it works, but I didn't really
want to go down this route if I can help it...I don't want to have to rely on
me (or other users) inserting names so the VBA can add them correctly...

Thanks as usual - this forum's been a superb resource for me. Top work you
guys!
George

Thanks JHyatt - useful tip that :-)

I can now see the named ranges are defined as

Data!ESFncTesters (with the sheet name before it)

How can I set a Named Range from VBA without this reference so that it's
usable in DV across all worksheets in the workbook?

i am no expert but maybe try.

1. On the Insert menu, point to Name, and then click Define.
2. In the Names in workbook box, type the name.
3. If the Refers to box contains a reference, select the equal sign (=) and
the reference and press BACKSPACE.
4. In the Refers to box, type = (equal sign).
5. Click the tab for the first worksheet to be referenced.
6. Hold down SHIFT and click the tab for the last worksheet to be referenced.
7. Select the cell or range of cells to be referenced.

"George" wrote:

Thanks JHyatt - useful tip that :-)

I can now see the named ranges are defined as

Data!ESFncTesters (with the sheet name before it)

How can I set a Named Range from VBA without this reference so that it's
usable in DV across all worksheets in the workbook?

That would probably work however I don't want to go down that route if
possible as when I need to add more ranges I (or a user) would need to add
the next named range to get it to work. I was hoping there would be a way to
create a completely new Named Range using VBA and have it accessible all
across the workbook in Excel without me needing to define it using the menu
system.

I'll keep looking around to see if there's anything I can find that will
help me out with this - in the short term however it looks like I'm going to
have to add them using "Insert Names Define" to make sure they
work...bummer :-(

Thanks for the earlier tip though JH!

have you tried recording a macro using this method to ficgure the code out
and then adjust it accordingly.

"George" wrote:

That would probably work however I don't want to go down that route if
possible as when I need to add more ranges I (or a user) would need to add
the next named range to get it to work. I was hoping there would be a way to
create a completely new Named Range using VBA and have it accessible all
across the workbook in Excel without me needing to define it using the menu
system.

I'll keep looking around to see if there's anything I can find that will
help me out with this - in the short term however it looks like I'm going to
have to add them using "Insert Names Define" to make sure they
work...bummer :-(

Thanks for the earlier tip though JH!

something like this

ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:= _
"=Sheet1:Sheet3!R1C1:R16C1"

"George" wrote:

That would probably work however I don't want to go down that route if
possible as when I need to add more ranges I (or a user) would need to add
the next named range to get it to work. I was hoping there would be a way to
create a completely new Named Range using VBA and have it accessible all
across the workbook in Excel without me needing to define it using the menu
system.

I'll keep looking around to see if there's anything I can find that will
help me out with this - in the short term however it looks like I'm going to
have to add them using "Insert Names Define" to make sure they
work...bummer :-(

Thanks for the earlier tip though JH!