dynamic range name

[Jonathan Cooper]

I'm looking for a HINT. I want to figure this out myself, but I'm stuck.

I wan't to start using dynamic range names for my pivot table data. But I
also don't want to have to type the formula in, over and over, because I
create them frequently.

I've created the following macro. I'm planning for the scenario, where I
have more than one database in a particular file. maybe sheet 1 has the
first data, and sheet two has different data. My thought is to run this
macro on sheet one and have it default to the name DataBase1. Then when I'm
ready, I run the macro on sheet 2 and it would autmatically create a name of
DataBase2. However, each time I run the macro, it defaults to DataBase1.
This is where I need the first hint.

Sub Macro1()
'
' Create a dynamic range name for my data, to be used in a pivot table.
' Macro recorded 4/5/2006 by Cooper
'

'
Dim DataName As String
On Error Resume Next

DataName = Application.InputBox("What do you want to call this range of
Data?", "Name your data", "Database" & cntr)
ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:= _
"=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),COUNTA(Sheet1!R1))"
cntr = cntr + 1
End Sub

I also realize that the formula specifically references "Sheet1", and that
when I run this thing on "Sheet2", it's going to be pointing to the wrong
location. I plan to tackle that next.

thanks in advance.

 

[Jonathan Cooper]

ok. I've figured out the second part of the problem, but not the first. My
updated macro is below. I just need to get the counter working....or some
other way to index the name so that it doesn't overlap.

Sub Macro1()
'
' Create a dynamic range name for my data, to be used in a pivot table.
' Macro recorded 4/5/2006 by Cooper
'

'
Dim ws As Worksheet
Dim wsName As String
Dim DataName As String
Dim Formula As String
On Error Resume Next

Set ws = ActiveSheet
wsName = ws.Name
Formula = "=Offset(" & wsName & "!R1C1, 0, 0, CountA(" & ws.Name & "!C1),
CountA(" & ws.Name & "!R1))"

DataName = Application.InputBox("What do you want to call this range of
Data?", _
"Name your data", "Database" & cntr)
ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula
End Sub

 

[Dave Peterson]

Instead of making the name a global name, how about making it a local name?
Then you can use the same name for each worksheet -- and drop the question to
the user????

ps.

Sometimes you used wsname and sometimes you use ws.name.

In either case, you'll want to be careful with those worksheet names that
require single quotes (like names with spaces or numeric names).

 

[Jonathan Cooper]

This is good. After I hit the 'post' button last time, I realized my screw
up on the ws.name. fixed that already.

What do you mean by 'local name'?

I'm SURE i'm doing this the hard way, but it's how I'll learn. I use
personal.xls to store all my utility macros; of which this will be one.

I was thinking of using a specific cell in personal.xls. then I could grab
the value from that cell each time, stick it in with the dataname, and then
just add 1 to that cells value.

Or...If I knew what I was doing, I could probably just check the name the
user inputs against the names collection of this workbook. If a name was
picked that was already used, it would error out and loop back for the user
to pick a different name.

another gentle nudge please.

 

[Dave Peterson]

Names can be local (worksheet level) or global (workbook level).

You can do a little experimentation when you add some names.

Create a test workbook with two sheets (sheet1 and sheet2).

Select A1 of Sheet1 and
Insert|name|Define
In the "Names in workbook" box, type this:
Sheet1!test1
refers to box: =Sheet1!$a$1

Then select A1 of Sheet2
Insert|name|Define
In the "Names in workbook" box, type this:
Sheet1!test1
refers to box: =Sheet2!$a$1

Each of these names (Sheet1!test1 and Sheet2!test1) are worksheet level names.

You can refer to them in any cell by:
=sheet1!test1
or
=sheet2!test1

(Or if you're on one of those sheets, you can omit the sheet name.)

Inside your VBA code, you can use:

dim myRng1 as range
dim myRng2 as range
set myrng1 = worksheets("sheet1").range("test1")
set myrng2 = worksheets("sheet2").range("test1")

It gives you the ability to use the same name in any worksheet.

After you've created those local names, select one of the worksheets.
Then do Insert|name
You'll see the name that is local to that sheet. In fact, you should see the
sheet name at the far right in that dialog.

If you don't see the sheetname for a name, then the name is global.

Using names that are duplicated can be confusing at first, but if they serve the
same purpose, then it may be easier in the long run. (You call the cell located
in A1, A1--no matter what sheet you're on, right? It's kind of the same thing.)

And working with the builtin dialog (Insert|Name|define) is a challenge in
itself. You don't see names local to a different sheet. You can't scroll right
or left to see more. That dialog is a pain (and will be made nicer in the next
version of excel.)

But until then, do yourself a favor and get Jan Karel Pieterse's (with Charles
Williams and Matthew Henson) Name Manager

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp


============
I recorded a macro when I inserted a local name. I got this:
ActiveWorkbook.Names.Add Name:="sheet1!test2", RefersToR1C1:="=Sheet1!R1C1"

This is equivalent to:
ActiveSheet.Names.Add Name:="test3", RefersToR1C1:="=sheet1!r1c1"

And this is equivalent to:
With ActiveSheet
.Range("A1").Name = "'" & .Name & "'!test4"
End With

===========
But working with these things becomes a pain when you mix local and global
names.

Here's one way to review the names:

Option Explicit
Sub testme()

Dim myName As Name
Dim myStr As String
Dim wks As Worksheet

myStr = "test"

Set myName = Nothing
On Error Resume Next
Set myName = ActiveWorkbook.Names(myStr)
On Error GoTo 0

If myName Is Nothing Then
MsgBox myStr & " isn't a global name"
Else
MsgBox myStr & " is a global name and it refers to: " _
& myName.RefersToRange.Address(external:=True)
End If

For Each wks In ActiveWorkbook.Worksheets
Set myName = Nothing
On Error Resume Next
Set myName = wks.Names(myStr)
On Error GoTo 0

If myName Is Nothing Then
'not on that sheet
Else
MsgBox myStr & " is a local name and it refers to: " _
& myName.RefersToRange.Address(external:=True)
End If
Next wks

End Sub

You'll notice that it goes through the activeworkbook.names collection first.
Then it looks through each worksheet names collection.

This is kind of equivalent--it loops through all the names looking for a match:

Option Explicit
Sub testme2()

Dim myName As Name
Dim myStr As String
Dim wks As Worksheet

myStr = "test"

For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) Like "*!" & LCase(myStr) Then
MsgBox "Local: " & myName.Name
Else
If LCase(myName.Name) Like LCase(myStr) Then
MsgBox "global: " & myName.Name
End If
End If
Next myName

End Sub

===================
I think I'd just use one name and do something like:

(scroll down when you're ready....)

..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..

Option Explicit
Sub testme3()

Dim myFormulaR1C1 As String
Dim wks As Worksheet
Dim myDBName As String

myDBName = "myDB"

For Each wks In ActiveWorkbook.Worksheets
myFormulaR1C1 = "=Offset('" & wks.Name & "'!R1C1,0,0," _
& "CountA('" & wks.Name & "'!C1),CountA('" _
& wks.Name & "'!R1))"
wks.Names.Add Name:=myDBName, RefersToR1C1:=myFormulaR1C1
Next wks

End Sub

 

[Jonathan Cooper]

Firstly.....THANK YOU VERY MUCH for spending this much time with me on this.
My goal is to learn to do my own basic VBA stuff, but even with Walkenbach's
book, I continue to struggle.

Hmmm...spent a lot of time digesting this. Back when XL97 came out, I
started toying around with using Names. The formula =SalesPrice x Quantity
was so much easier to understand than =A2 * E2, and for non-technical users
it was easier for them to understnad. But I started running into errors and
scrapped the whole idea. I haven't put any thought into them since then, but
the dynamic range name idea would solve some pivot table problems so I'm
jumping back in.

I now understand what you mean by global and local. So, when you create the
name Sheet1!test1.....the fact that you put in 'SHEET1!' in the name, tells
excel to make that local (i.e. specific to that worksheet). If I'm on sheet1
but my name is just 'test1', then it's global. cool but so what? What does
this do for anyone? How could you use it to your advantage?

I suppose that if if my data was on sheet1, and I called it Sheet1!Data,
then i'll get an error if I'm on any other worksheet and try to name it
Sheet1!Data.

If my pivot tables were on the same worksheet as my data, then I would just
reference test1 when setting up the pivot table and it would work
consistently.

But I normally set up the pivot table on a sepreate worksheet from my data.
Thus typing test1 in the pivot table range dialog will not work for me. I'll
have to manually type Sheet1!test1 (a local name) to get it to work.

this brings me back to thinking that I need to either index the default name
I'm using, or figure out how to check the name that is choosen and see if it
already exists. If it does, then ask the user if they want to overwrite or
pick a different name. The former seems within my grasp, the later seems out
of reach at the moment.

 

[Dave Peterson]

By using a sheet level name, you can use the same name (without the sheet name)
for each sheet. Why not just refer to the sheetlevel name when you're building
the pivottable?

I don't see why the users would have to be involved at all.

An alternative if you want to use global names is to just make them unique
yourself:

sheet1MyDB
sheet2MyDB
Sheet3MyDB

Note that I really meant to include the sheet name with the name--not
Sheet1!myDB.