software

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(quot;What do you want to call this range of
Data?quot;, quot;Name your dataquot;, quot;Databasequot; amp; cntr)
ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:= _
quot;=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),COUNTA( Sheet1!R1))quot;
cntr = cntr 1
End Sub

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

thanks in advance.

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 = quot;=Offset(quot; amp; wsName amp; quot;!R1C1, 0, 0, CountA(quot; amp; ws.Name amp; quot;!C1),
CountA(quot; amp; ws.Name amp; quot;!R1))quot;

DataName = Application.InputBox(quot;What do you want to call this range of
Data?quot;, _
quot;Name your dataquot;, quot;Databasequot; amp; cntr)
ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula
End Subquot;Jonathan Cooperquot; wrote:

gt; I'm looking for a HINT. I want to figure this out myself, but I'm stuck.
gt;
gt; I wan't to start using dynamic range names for my pivot table data. But I
gt; also don't want to have to type the formula in, over and over, because I
gt; create them frequently.
gt;
gt; I've created the following macro. I'm planning for the scenario, where I
gt; have more than one database in a particular file. maybe sheet 1 has the
gt; first data, and sheet two has different data. My thought is to run this
gt; macro on sheet one and have it default to the name DataBase1. Then when I'm
gt; ready, I run the macro on sheet 2 and it would autmatically create a name of
gt; DataBase2. However, each time I run the macro, it defaults to DataBase1.
gt; This is where I need the first hint.
gt;
gt; Sub Macro1()
gt; '
gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; ' Macro recorded 4/5/2006 by Cooper
gt; '
gt;
gt; '
gt; Dim DataName As String
gt; On Error Resume Next
gt;
gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; Data?quot;, quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:= _
gt; quot;=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),COUNTA( Sheet1!R1))quot;
gt; cntr = cntr 1
gt; End Sub
gt;
gt; I also realize that the formula specifically references quot;Sheet1quot;, and that
gt; when I run this thing on quot;Sheet2quot;, it's going to be pointing to the wrong
gt; location. I plan to tackle that next.
gt;
gt; thanks in advance.

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 wrote:
gt;
gt; ok. I've figured out the second part of the problem, but not the first. My
gt; updated macro is below. I just need to get the counter working....or some
gt; other way to index the name so that it doesn't overlap.
gt;
gt; Sub Macro1()
gt; '
gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; ' Macro recorded 4/5/2006 by Cooper
gt; '
gt;
gt; '
gt; Dim ws As Worksheet
gt; Dim wsName As String
gt; Dim DataName As String
gt; Dim Formula As String
gt; On Error Resume Next
gt;
gt; Set ws = ActiveSheet
gt; wsName = ws.Name
gt; Formula = quot;=Offset(quot; amp; wsName amp; quot;!R1C1, 0, 0, CountA(quot; amp; ws.Name amp; quot;!C1),
gt; CountA(quot; amp; ws.Name amp; quot;!R1))quot;
gt;
gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; Data?quot;, _
gt; quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula
gt; End Sub
gt;
gt; quot;Jonathan Cooperquot; wrote:
gt;
gt; gt; I'm looking for a HINT. I want to figure this out myself, but I'm stuck.
gt; gt;
gt; gt; I wan't to start using dynamic range names for my pivot table data. But I
gt; gt; also don't want to have to type the formula in, over and over, because I
gt; gt; create them frequently.
gt; gt;
gt; gt; I've created the following macro. I'm planning for the scenario, where I
gt; gt; have more than one database in a particular file. maybe sheet 1 has the
gt; gt; first data, and sheet two has different data. My thought is to run this
gt; gt; macro on sheet one and have it default to the name DataBase1. Then when I'm
gt; gt; ready, I run the macro on sheet 2 and it would autmatically create a name of
gt; gt; DataBase2. However, each time I run the macro, it defaults to DataBase1.
gt; gt; This is where I need the first hint.
gt; gt;
gt; gt; Sub Macro1()
gt; gt; '
gt; gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; gt; ' Macro recorded 4/5/2006 by Cooper
gt; gt; '
gt; gt;
gt; gt; '
gt; gt; Dim DataName As String
gt; gt; On Error Resume Next
gt; gt;
gt; gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; gt; Data?quot;, quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:= _
gt; gt; quot;=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),COUNTA( Sheet1!R1))quot;
gt; gt; cntr = cntr 1
gt; gt; End Sub
gt; gt;
gt; gt; I also realize that the formula specifically references quot;Sheet1quot;, and that
gt; gt; when I run this thing on quot;Sheet2quot;, it's going to be pointing to the wrong
gt; gt; location. I plan to tackle that next.
gt; gt;
gt; gt; thanks in advance.

--

Dave Peterson

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.quot;Dave Petersonquot; wrote:

gt; Instead of making the name a global name, how about making it a local name?
gt; Then you can use the same name for each worksheet -- and drop the question to
gt; the user????
gt;
gt; ps.
gt;
gt; Sometimes you used wsname and sometimes you use ws.name.
gt;
gt; In either case, you'll want to be careful with those worksheet names that
gt; require single quotes (like names with spaces or numeric names).
gt;
gt;
gt;
gt; Jonathan Cooper wrote:
gt; gt;
gt; gt; ok. I've figured out the second part of the problem, but not the first. My
gt; gt; updated macro is below. I just need to get the counter working....or some
gt; gt; other way to index the name so that it doesn't overlap.
gt; gt;
gt; gt; Sub Macro1()
gt; gt; '
gt; gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; gt; ' Macro recorded 4/5/2006 by Cooper
gt; gt; '
gt; gt;
gt; gt; '
gt; gt; Dim ws As Worksheet
gt; gt; Dim wsName As String
gt; gt; Dim DataName As String
gt; gt; Dim Formula As String
gt; gt; On Error Resume Next
gt; gt;
gt; gt; Set ws = ActiveSheet
gt; gt; wsName = ws.Name
gt; gt; Formula = quot;=Offset(quot; amp; wsName amp; quot;!R1C1, 0, 0, CountA(quot; amp; ws.Name amp; quot;!C1),
gt; gt; CountA(quot; amp; ws.Name amp; quot;!R1))quot;
gt; gt;
gt; gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; gt; Data?quot;, _
gt; gt; quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula
gt; gt; End Sub
gt; gt;
gt; gt; quot;Jonathan Cooperquot; wrote:
gt; gt;
gt; gt; gt; I'm looking for a HINT. I want to figure this out myself, but I'm stuck.
gt; gt; gt;
gt; gt; gt; I wan't to start using dynamic range names for my pivot table data. But I
gt; gt; gt; also don't want to have to type the formula in, over and over, because I
gt; gt; gt; create them frequently.
gt; gt; gt;
gt; gt; gt; I've created the following macro. I'm planning for the scenario, where I
gt; gt; gt; have more than one database in a particular file. maybe sheet 1 has the
gt; gt; gt; first data, and sheet two has different data. My thought is to run this
gt; gt; gt; macro on sheet one and have it default to the name DataBase1. Then when I'm
gt; gt; gt; ready, I run the macro on sheet 2 and it would autmatically create a name of
gt; gt; gt; DataBase2. However, each time I run the macro, it defaults to DataBase1.
gt; gt; gt; This is where I need the first hint.
gt; gt; gt;
gt; gt; gt; Sub Macro1()
gt; gt; gt; '
gt; gt; gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; gt; gt; ' Macro recorded 4/5/2006 by Cooper
gt; gt; gt; '
gt; gt; gt;
gt; gt; gt; '
gt; gt; gt; Dim DataName As String
gt; gt; gt; On Error Resume Next
gt; gt; gt;
gt; gt; gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; gt; gt; Data?quot;, quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; gt; gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:= _
gt; gt; gt; quot;=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),COUNTA( Sheet1!R1))quot;
gt; gt; gt; cntr = cntr 1
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; I also realize that the formula specifically references quot;Sheet1quot;, and that
gt; gt; gt; when I run this thing on quot;Sheet2quot;, it's going to be pointing to the wrong
gt; gt; gt; location. I plan to tackle that next.
gt; gt; gt;
gt; gt; gt; thanks in advance.
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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 quot;Names in workbookquot; box, type this:
Sheet1!test1
refers to box: =Sheet1!$a$1

Then select A1 of Sheet2
Insert|name|Define
In the quot;Names in workbookquot; 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(quot;sheet1quot;).range(quot;test1quot;)
set myrng2 = worksheets(quot;sheet2quot;).range(quot;test1quot;)

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 www.oaltd.co.uk/mvp============
I recorded a macro when I inserted a local name. I got this:
ActiveWorkbook.Names.Add Name:=quot;sheet1!test2quot;, RefersToR1C1:=quot;=Sheet1!R1C1quot;

This is equivalent to:
ActiveSheet.Names.Add Name:=quot;test3quot;, RefersToR1C1:=quot;=sheet1!r1c1quot;

And this is equivalent to:
With ActiveSheet
.Range(quot;A1quot;).Name = quot;'quot; amp; .Name amp; quot;'!test4quot;
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 = quot;testquot;

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

If myName Is Nothing Then
MsgBox myStr amp; quot; isn't a global namequot;
Else
MsgBox myStr amp; quot; is a global name and it refers to: quot; _
amp; 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 amp; quot; is a local name and it refers to: quot; _
amp; 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 = quot;testquot;

For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) Like quot;*!quot; amp; LCase(myStr) Then
MsgBox quot;Local: quot; amp; myName.Name
Else
If LCase(myName.Name) Like LCase(myStr) Then
MsgBox quot;global: quot; amp; 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 = quot;myDBquot;

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

End SubJonathan Cooper wrote:
gt;
gt; This is good. After I hit the 'post' button last time, I realized my screw
gt; up on the ws.name. fixed that already.
gt;
gt; What do you mean by 'local name'?
gt;
gt; I'm SURE i'm doing this the hard way, but it's how I'll learn. I use
gt; personal.xls to store all my utility macros; of which this will be one.
gt;
gt; I was thinking of using a specific cell in personal.xls. then I could grab
gt; the value from that cell each time, stick it in with the dataname, and then
gt; just add 1 to that cells value.
gt;
gt; Or...If I knew what I was doing, I could probably just check the name the
gt; user inputs against the names collection of this workbook. If a name was
gt; picked that was already used, it would error out and loop back for the user
gt; to pick a different name.
gt;
gt; another gentle nudge please.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Instead of making the name a global name, how about making it a local name?
gt; gt; Then you can use the same name for each worksheet -- and drop the question to
gt; gt; the user????
gt; gt;
gt; gt; ps.
gt; gt;
gt; gt; Sometimes you used wsname and sometimes you use ws.name.
gt; gt;
gt; gt; In either case, you'll want to be careful with those worksheet names that
gt; gt; require single quotes (like names with spaces or numeric names).
gt; gt;
gt; gt;
gt; gt;
gt; gt; Jonathan Cooper wrote:
gt; gt; gt;
gt; gt; gt; ok. I've figured out the second part of the problem, but not the first. My
gt; gt; gt; updated macro is below. I just need to get the counter working....or some
gt; gt; gt; other way to index the name so that it doesn't overlap.
gt; gt; gt;
gt; gt; gt; Sub Macro1()
gt; gt; gt; '
gt; gt; gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; gt; gt; ' Macro recorded 4/5/2006 by Cooper
gt; gt; gt; '
gt; gt; gt;
gt; gt; gt; '
gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; Dim wsName As String
gt; gt; gt; Dim DataName As String
gt; gt; gt; Dim Formula As String
gt; gt; gt; On Error Resume Next
gt; gt; gt;
gt; gt; gt; Set ws = ActiveSheet
gt; gt; gt; wsName = ws.Name
gt; gt; gt; Formula = quot;=Offset(quot; amp; wsName amp; quot;!R1C1, 0, 0, CountA(quot; amp; ws.Name amp; quot;!C1),
gt; gt; gt; CountA(quot; amp; ws.Name amp; quot;!R1))quot;
gt; gt; gt;
gt; gt; gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; gt; gt; Data?quot;, _
gt; gt; gt; quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; gt; gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; quot;Jonathan Cooperquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I'm looking for a HINT. I want to figure this out myself, but I'm stuck.
gt; gt; gt; gt;
gt; gt; gt; gt; I wan't to start using dynamic range names for my pivot table data. But I
gt; gt; gt; gt; also don't want to have to type the formula in, over and over, because I
gt; gt; gt; gt; create them frequently.
gt; gt; gt; gt;
gt; gt; gt; gt; I've created the following macro. I'm planning for the scenario, where I
gt; gt; gt; gt; have more than one database in a particular file. maybe sheet 1 has the
gt; gt; gt; gt; first data, and sheet two has different data. My thought is to run this
gt; gt; gt; gt; macro on sheet one and have it default to the name DataBase1. Then when I'm
gt; gt; gt; gt; ready, I run the macro on sheet 2 and it would autmatically create a name of
gt; gt; gt; gt; DataBase2. However, each time I run the macro, it defaults to DataBase1.
gt; gt; gt; gt; This is where I need the first hint.
gt; gt; gt; gt;
gt; gt; gt; gt; Sub Macro1()
gt; gt; gt; gt; '
gt; gt; gt; gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; gt; gt; gt; ' Macro recorded 4/5/2006 by Cooper
gt; gt; gt; gt; '
gt; gt; gt; gt;
gt; gt; gt; gt; '
gt; gt; gt; gt; Dim DataName As String
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt;
gt; gt; gt; gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; gt; gt; gt; Data?quot;, quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; gt; gt; gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:= _
gt; gt; gt; gt; quot;=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),COUNTA( Sheet1!R1))quot;
gt; gt; gt; gt; cntr = cntr 1
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; I also realize that the formula specifically references quot;Sheet1quot;, and that
gt; gt; gt; gt; when I run this thing on quot;Sheet2quot;, it's going to be pointing to the wrong
gt; gt; gt; gt; location. I plan to tackle that next.
gt; gt; gt; gt;
gt; gt; gt; gt; thanks in advance.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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.quot;Dave Petersonquot; wrote:

gt; Names can be local (worksheet level) or global (workbook level).
gt;
gt; You can do a little experimentation when you add some names.
gt;
gt; Create a test workbook with two sheets (sheet1 and sheet2).
gt;
gt; Select A1 of Sheet1 and
gt; Insert|name|Define
gt; In the quot;Names in workbookquot; box, type this:
gt; Sheet1!test1
gt; refers to box: =Sheet1!$a$1
gt;
gt; Then select A1 of Sheet2
gt; Insert|name|Define
gt; In the quot;Names in workbookquot; box, type this:
gt; Sheet1!test1
gt; refers to box: =Sheet2!$a$1
gt;
gt; Each of these names (Sheet1!test1 and Sheet2!test1) are worksheet level names.
gt;
gt; You can refer to them in any cell by:
gt; =sheet1!test1
gt; or
gt; =sheet2!test1
gt;
gt; (Or if you're on one of those sheets, you can omit the sheet name.)
gt;
gt; Inside your VBA code, you can use:
gt;
gt; dim myRng1 as range
gt; dim myRng2 as range
gt; set myrng1 = worksheets(quot;sheet1quot;).range(quot;test1quot;)
gt; set myrng2 = worksheets(quot;sheet2quot;).range(quot;test1quot;)
gt;
gt; It gives you the ability to use the same name in any worksheet.
gt;
gt; After you've created those local names, select one of the worksheets.
gt; Then do Insert|name
gt; You'll see the name that is local to that sheet. In fact, you should see the
gt; sheet name at the far right in that dialog.
gt;
gt; If you don't see the sheetname for a name, then the name is global.
gt;
gt; Using names that are duplicated can be confusing at first, but if they serve the
gt; same purpose, then it may be easier in the long run. (You call the cell located
gt; in A1, A1--no matter what sheet you're on, right? It's kind of the same thing.)
gt;
gt; And working with the builtin dialog (Insert|Name|define) is a challenge in
gt; itself. You don't see names local to a different sheet. You can't scroll right
gt; or left to see more. That dialog is a pain (and will be made nicer in the next
gt; version of excel.)
gt;
gt; But until then, do yourself a favor and get Jan Karel Pieterse's (with Charles
gt; Williams and Matthew Henson) Name Manager
gt;
gt; You can find it at:
gt; NameManager.Zip from www.oaltd.co.uk/mvp
gt;
gt;
gt; ============
gt; I recorded a macro when I inserted a local name. I got this:
gt; ActiveWorkbook.Names.Add Name:=quot;sheet1!test2quot;, RefersToR1C1:=quot;=Sheet1!R1C1quot;
gt;
gt; This is equivalent to:
gt; ActiveSheet.Names.Add Name:=quot;test3quot;, RefersToR1C1:=quot;=sheet1!r1c1quot;
gt;
gt; And this is equivalent to:
gt; With ActiveSheet
gt; .Range(quot;A1quot;).Name = quot;'quot; amp; .Name amp; quot;'!test4quot;
gt; End With
gt;
gt; ===========
gt; But working with these things becomes a pain when you mix local and global
gt; names.
gt;
gt; Here's one way to review the names:
gt;
gt; Option Explicit
gt; Sub testme()
gt;
gt; Dim myName As Name
gt; Dim myStr As String
gt; Dim wks As Worksheet
gt;
gt; myStr = quot;testquot;
gt;
gt; Set myName = Nothing
gt; On Error Resume Next
gt; Set myName = ActiveWorkbook.Names(myStr)
gt; On Error GoTo 0
gt;
gt; If myName Is Nothing Then
gt; MsgBox myStr amp; quot; isn't a global namequot;
gt; Else
gt; MsgBox myStr amp; quot; is a global name and it refers to: quot; _
gt; amp; myName.RefersToRange.Address(external:=True)
gt; End If
gt;
gt; For Each wks In ActiveWorkbook.Worksheets
gt; Set myName = Nothing
gt; On Error Resume Next
gt; Set myName = wks.Names(myStr)
gt; On Error GoTo 0
gt;
gt; If myName Is Nothing Then
gt; 'not on that sheet
gt; Else
gt; MsgBox myStr amp; quot; is a local name and it refers to: quot; _
gt; amp; myName.RefersToRange.Address(external:=True)
gt; End If
gt; Next wks
gt;
gt; End Sub
gt;
gt; You'll notice that it goes through the activeworkbook.names collection first.
gt; Then it looks through each worksheet names collection.
gt;
gt; This is kind of equivalent--it loops through all the names looking for a match:
gt;
gt; Option Explicit
gt; Sub testme2()
gt;
gt; Dim myName As Name
gt; Dim myStr As String
gt; Dim wks As Worksheet
gt;
gt; myStr = quot;testquot;
gt;
gt; For Each myName In ActiveWorkbook.Names
gt; If LCase(myName.Name) Like quot;*!quot; amp; LCase(myStr) Then
gt; MsgBox quot;Local: quot; amp; myName.Name
gt; Else
gt; If LCase(myName.Name) Like LCase(myStr) Then
gt; MsgBox quot;global: quot; amp; myName.Name
gt; End If
gt; End If
gt; Next myName
gt;
gt; End Sub
gt;
gt; ===================
gt; I think I'd just use one name and do something like:
gt;
gt; (scroll down when you're ready....)
gt;
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt; ..
gt;
gt; Option Explicit
gt; Sub testme3()
gt;
gt; Dim myFormulaR1C1 As String
gt; Dim wks As Worksheet
gt; Dim myDBName As String
gt;
gt; myDBName = quot;myDBquot;
gt;
gt; For Each wks In ActiveWorkbook.Worksheets
gt; myFormulaR1C1 = quot;=Offset('quot; amp; wks.Name amp; quot;'!R1C1,0,0,quot; _
gt; amp; quot;CountA('quot; amp; wks.Name amp; quot;'!C1),CountA('quot; _
gt; amp; wks.Name amp; quot;'!R1))quot;
gt; wks.Names.Add Name:=myDBName, RefersToR1C1:=myFormulaR1C1
gt; Next wks
gt;
gt; End Sub
gt;
gt;
gt; Jonathan Cooper wrote:
gt; gt;
gt; gt; This is good. After I hit the 'post' button last time, I realized my screw
gt; gt; up on the ws.name. fixed that already.
gt; gt;
gt; gt; What do you mean by 'local name'?
gt; gt;
gt; gt; I'm SURE i'm doing this the hard way, but it's how I'll learn. I use
gt; gt; personal.xls to store all my utility macros; of which this will be one.
gt; gt;
gt; gt; I was thinking of using a specific cell in personal.xls. then I could grab
gt; gt; the value from that cell each time, stick it in with the dataname, and then
gt; gt; just add 1 to that cells value.
gt; gt;
gt; gt; Or...If I knew what I was doing, I could probably just check the name the
gt; gt; user inputs against the names collection of this workbook. If a name was
gt; gt; picked that was already used, it would error out and loop back for the user
gt; gt; to pick a different name.
gt; gt;
gt; gt; another gentle nudge please.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; Instead of making the name a global name, how about making it a local name?
gt; gt; gt; Then you can use the same name for each worksheet -- and drop the question to
gt; gt; gt; the user????
gt; gt; gt;
gt; gt; gt; ps.
gt; gt; gt;
gt; gt; gt; Sometimes you used wsname and sometimes you use ws.name.
gt; gt; gt;
gt; gt; gt; In either case, you'll want to be careful with those worksheet names that
gt; gt; gt; require single quotes (like names with spaces or numeric names).
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Jonathan Cooper wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; ok. I've figured out the second part of the problem, but not the first. My
gt; gt; gt; gt; updated macro is below. I just need to get the counter working....or some
gt; gt; gt; gt; other way to index the name so that it doesn't overlap.
gt; gt; gt; gt;
gt; gt; gt; gt; Sub Macro1()
gt; gt; gt; gt; '
gt; gt; gt; gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; gt; gt; gt; ' Macro recorded 4/5/2006 by Cooper
gt; gt; gt; gt; '
gt; gt; gt; gt;
gt; gt; gt; gt; '
gt; gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; gt; Dim wsName As String
gt; gt; gt; gt; Dim DataName As String
gt; gt; gt; gt; Dim Formula As String
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt;
gt; gt; gt; gt; Set ws = ActiveSheet
gt; gt; gt; gt; wsName = ws.Name
gt; gt; gt; gt; Formula = quot;=Offset(quot; amp; wsName amp; quot;!R1C1, 0, 0, CountA(quot; amp; ws.Name amp; quot;!C1),
gt; gt; gt; gt; CountA(quot; amp; ws.Name amp; quot;!R1))quot;
gt; gt; gt; gt;
gt; gt; gt; gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; gt; gt; gt; Data?quot;, _
gt; gt; gt; gt; quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; gt; gt; gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Jonathan Cooperquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I'm looking for a HINT. I want to figure this out myself, but I'm stuck.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I wan't to start using dynamic range names for my pivot table data. But I
gt; gt; gt; gt; gt; also don't want to have to type the formula in, over and over, because I
gt; gt; gt; gt; gt; create them frequently.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I've created the following macro. I'm planning for the scenario, where I
gt; gt; gt; gt; gt; have more than one database in a particular file. maybe sheet 1 has the
gt; gt; gt; gt; gt; first data, and sheet two has different data. My thought is to run this
gt; gt; gt; gt; gt; macro on sheet one and have it default to the name DataBase1. Then when I'm
gt; gt; gt; gt; gt; ready, I run the macro on sheet 2 and it would autmatically create a name of
gt; gt; gt; gt; gt; DataBase2. However, each time I run the macro, it defaults to DataBase1.
gt; gt; gt; gt; gt; This is where I need the first hint.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Sub Macro1()
gt; gt; gt; gt; gt; '
gt; gt; gt; gt; gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; gt; gt; gt; gt; ' Macro recorded 4/5/2006 by Cooper
gt; gt; gt; gt; gt; '
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; '
gt; gt; gt; gt; gt; Dim DataName As String
gt; gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; gt; gt; gt; gt; Data?quot;, quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; gt; gt; gt; gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:= _
gt; gt; gt; gt; gt; quot;=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),COUNTA( Sheet1!R1))quot;
gt; gt; gt; gt; gt; cntr = cntr 1
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I also realize that the formula specifically references quot;Sheet1quot;, and that
gt; gt; gt; gt; gt; when I run this thing on quot;Sheet2quot;, it's going to be pointing to the wrong
gt; gt; gt; gt; gt; location. I plan to tackle that next.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; thanks in advance.
gt; gt; gt;

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.
Jonathan Cooper wrote:
gt;
gt; Firstly.....THANK YOU VERY MUCH for spending this much time with me on this.
gt; My goal is to learn to do my own basic VBA stuff, but even with Walkenbach's
gt; book, I continue to struggle.
gt;
gt; Hmmm...spent a lot of time digesting this. Back when XL97 came out, I
gt; started toying around with using Names. The formula =SalesPrice x Quantity
gt; was so much easier to understand than =A2 * E2, and for non-technical users
gt; it was easier for them to understnad. But I started running into errors and
gt; scrapped the whole idea. I haven't put any thought into them since then, but
gt; the dynamic range name idea would solve some pivot table problems so I'm
gt; jumping back in.
gt;
gt; I now understand what you mean by global and local. So, when you create the
gt; name Sheet1!test1.....the fact that you put in 'SHEET1!' in the name, tells
gt; excel to make that local (i.e. specific to that worksheet). If I'm on sheet1
gt; but my name is just 'test1', then it's global. cool but so what? What does
gt; this do for anyone? How could you use it to your advantage?
gt;
gt; I suppose that if if my data was on sheet1, and I called it Sheet1!Data,
gt; then i'll get an error if I'm on any other worksheet and try to name it
gt; Sheet1!Data.
gt;
gt; If my pivot tables were on the same worksheet as my data, then I would just
gt; reference test1 when setting up the pivot table and it would work
gt; consistently.
gt;
gt; But I normally set up the pivot table on a sepreate worksheet from my data.
gt; Thus typing test1 in the pivot table range dialog will not work for me. I'll
gt; have to manually type Sheet1!test1 (a local name) to get it to work.
gt;
gt; this brings me back to thinking that I need to either index the default name
gt; I'm using, or figure out how to check the name that is choosen and see if it
gt; already exists. If it does, then ask the user if they want to overwrite or
gt; pick a different name. The former seems within my grasp, the later seems out
gt; of reach at the moment.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Names can be local (worksheet level) or global (workbook level).
gt; gt;
gt; gt; You can do a little experimentation when you add some names.
gt; gt;
gt; gt; Create a test workbook with two sheets (sheet1 and sheet2).
gt; gt;
gt; gt; Select A1 of Sheet1 and
gt; gt; Insert|name|Define
gt; gt; In the quot;Names in workbookquot; box, type this:
gt; gt; Sheet1!test1
gt; gt; refers to box: =Sheet1!$a$1
gt; gt;
gt; gt; Then select A1 of Sheet2
gt; gt; Insert|name|Define
gt; gt; In the quot;Names in workbookquot; box, type this:
gt; gt; Sheet1!test1
gt; gt; refers to box: =Sheet2!$a$1
gt; gt;
gt; gt; Each of these names (Sheet1!test1 and Sheet2!test1) are worksheet level names.
gt; gt;
gt; gt; You can refer to them in any cell by:
gt; gt; =sheet1!test1
gt; gt; or
gt; gt; =sheet2!test1
gt; gt;
gt; gt; (Or if you're on one of those sheets, you can omit the sheet name.)
gt; gt;
gt; gt; Inside your VBA code, you can use:
gt; gt;
gt; gt; dim myRng1 as range
gt; gt; dim myRng2 as range
gt; gt; set myrng1 = worksheets(quot;sheet1quot;).range(quot;test1quot;)
gt; gt; set myrng2 = worksheets(quot;sheet2quot;).range(quot;test1quot;)
gt; gt;
gt; gt; It gives you the ability to use the same name in any worksheet.
gt; gt;
gt; gt; After you've created those local names, select one of the worksheets.
gt; gt; Then do Insert|name
gt; gt; You'll see the name that is local to that sheet. In fact, you should see the
gt; gt; sheet name at the far right in that dialog.
gt; gt;
gt; gt; If you don't see the sheetname for a name, then the name is global.
gt; gt;
gt; gt; Using names that are duplicated can be confusing at first, but if they serve the
gt; gt; same purpose, then it may be easier in the long run. (You call the cell located
gt; gt; in A1, A1--no matter what sheet you're on, right? It's kind of the same thing.)
gt; gt;
gt; gt; And working with the builtin dialog (Insert|Name|define) is a challenge in
gt; gt; itself. You don't see names local to a different sheet. You can't scroll right
gt; gt; or left to see more. That dialog is a pain (and will be made nicer in the next
gt; gt; version of excel.)
gt; gt;
gt; gt; But until then, do yourself a favor and get Jan Karel Pieterse's (with Charles
gt; gt; Williams and Matthew Henson) Name Manager
gt; gt;
gt; gt; You can find it at:
gt; gt; NameManager.Zip from www.oaltd.co.uk/mvp
gt; gt;
gt; gt;
gt; gt; ============
gt; gt; I recorded a macro when I inserted a local name. I got this:
gt; gt; ActiveWorkbook.Names.Add Name:=quot;sheet1!test2quot;, RefersToR1C1:=quot;=Sheet1!R1C1quot;
gt; gt;
gt; gt; This is equivalent to:
gt; gt; ActiveSheet.Names.Add Name:=quot;test3quot;, RefersToR1C1:=quot;=sheet1!r1c1quot;
gt; gt;
gt; gt; And this is equivalent to:
gt; gt; With ActiveSheet
gt; gt; .Range(quot;A1quot;).Name = quot;'quot; amp; .Name amp; quot;'!test4quot;
gt; gt; End With
gt; gt;
gt; gt; ===========
gt; gt; But working with these things becomes a pain when you mix local and global
gt; gt; names.
gt; gt;
gt; gt; Here's one way to review the names:
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testme()
gt; gt;
gt; gt; Dim myName As Name
gt; gt; Dim myStr As String
gt; gt; Dim wks As Worksheet
gt; gt;
gt; gt; myStr = quot;testquot;
gt; gt;
gt; gt; Set myName = Nothing
gt; gt; On Error Resume Next
gt; gt; Set myName = ActiveWorkbook.Names(myStr)
gt; gt; On Error GoTo 0
gt; gt;
gt; gt; If myName Is Nothing Then
gt; gt; MsgBox myStr amp; quot; isn't a global namequot;
gt; gt; Else
gt; gt; MsgBox myStr amp; quot; is a global name and it refers to: quot; _
gt; gt; amp; myName.RefersToRange.Address(external:=True)
gt; gt; End If
gt; gt;
gt; gt; For Each wks In ActiveWorkbook.Worksheets
gt; gt; Set myName = Nothing
gt; gt; On Error Resume Next
gt; gt; Set myName = wks.Names(myStr)
gt; gt; On Error GoTo 0
gt; gt;
gt; gt; If myName Is Nothing Then
gt; gt; 'not on that sheet
gt; gt; Else
gt; gt; MsgBox myStr amp; quot; is a local name and it refers to: quot; _
gt; gt; amp; myName.RefersToRange.Address(external:=True)
gt; gt; End If
gt; gt; Next wks
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; You'll notice that it goes through the activeworkbook.names collection first.
gt; gt; Then it looks through each worksheet names collection.
gt; gt;
gt; gt; This is kind of equivalent--it loops through all the names looking for a match:
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testme2()
gt; gt;
gt; gt; Dim myName As Name
gt; gt; Dim myStr As String
gt; gt; Dim wks As Worksheet
gt; gt;
gt; gt; myStr = quot;testquot;
gt; gt;
gt; gt; For Each myName In ActiveWorkbook.Names
gt; gt; If LCase(myName.Name) Like quot;*!quot; amp; LCase(myStr) Then
gt; gt; MsgBox quot;Local: quot; amp; myName.Name
gt; gt; Else
gt; gt; If LCase(myName.Name) Like LCase(myStr) Then
gt; gt; MsgBox quot;global: quot; amp; myName.Name
gt; gt; End If
gt; gt; End If
gt; gt; Next myName
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; ===================
gt; gt; I think I'd just use one name and do something like:
gt; gt;
gt; gt; (scroll down when you're ready....)
gt; gt;
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt; ..
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testme3()
gt; gt;
gt; gt; Dim myFormulaR1C1 As String
gt; gt; Dim wks As Worksheet
gt; gt; Dim myDBName As String
gt; gt;
gt; gt; myDBName = quot;myDBquot;
gt; gt;
gt; gt; For Each wks In ActiveWorkbook.Worksheets
gt; gt; myFormulaR1C1 = quot;=Offset('quot; amp; wks.Name amp; quot;'!R1C1,0,0,quot; _
gt; gt; amp; quot;CountA('quot; amp; wks.Name amp; quot;'!C1),CountA('quot; _
gt; gt; amp; wks.Name amp; quot;'!R1))quot;
gt; gt; wks.Names.Add Name:=myDBName, RefersToR1C1:=myFormulaR1C1
gt; gt; Next wks
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; Jonathan Cooper wrote:
gt; gt; gt;
gt; gt; gt; This is good. After I hit the 'post' button last time, I realized my screw
gt; gt; gt; up on the ws.name. fixed that already.
gt; gt; gt;
gt; gt; gt; What do you mean by 'local name'?
gt; gt; gt;
gt; gt; gt; I'm SURE i'm doing this the hard way, but it's how I'll learn. I use
gt; gt; gt; personal.xls to store all my utility macros; of which this will be one.
gt; gt; gt;
gt; gt; gt; I was thinking of using a specific cell in personal.xls. then I could grab
gt; gt; gt; the value from that cell each time, stick it in with the dataname, and then
gt; gt; gt; just add 1 to that cells value.
gt; gt; gt;
gt; gt; gt; Or...If I knew what I was doing, I could probably just check the name the
gt; gt; gt; user inputs against the names collection of this workbook. If a name was
gt; gt; gt; picked that was already used, it would error out and loop back for the user
gt; gt; gt; to pick a different name.
gt; gt; gt;
gt; gt; gt; another gentle nudge please.
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Instead of making the name a global name, how about making it a local name?
gt; gt; gt; gt; Then you can use the same name for each worksheet -- and drop the question to
gt; gt; gt; gt; the user????
gt; gt; gt; gt;
gt; gt; gt; gt; ps.
gt; gt; gt; gt;
gt; gt; gt; gt; Sometimes you used wsname and sometimes you use ws.name.
gt; gt; gt; gt;
gt; gt; gt; gt; In either case, you'll want to be careful with those worksheet names that
gt; gt; gt; gt; require single quotes (like names with spaces or numeric names).
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Jonathan Cooper wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; ok. I've figured out the second part of the problem, but not the first. My
gt; gt; gt; gt; gt; updated macro is below. I just need to get the counter working....or some
gt; gt; gt; gt; gt; other way to index the name so that it doesn't overlap.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Sub Macro1()
gt; gt; gt; gt; gt; '
gt; gt; gt; gt; gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; gt; gt; gt; gt; ' Macro recorded 4/5/2006 by Cooper
gt; gt; gt; gt; gt; '
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; '
gt; gt; gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; gt; gt; Dim wsName As String
gt; gt; gt; gt; gt; Dim DataName As String
gt; gt; gt; gt; gt; Dim Formula As String
gt; gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Set ws = ActiveSheet
gt; gt; gt; gt; gt; wsName = ws.Name
gt; gt; gt; gt; gt; Formula = quot;=Offset(quot; amp; wsName amp; quot;!R1C1, 0, 0, CountA(quot; amp; ws.Name amp; quot;!C1),
gt; gt; gt; gt; gt; CountA(quot; amp; ws.Name amp; quot;!R1))quot;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; gt; gt; gt; gt; Data?quot;, _
gt; gt; gt; gt; gt; quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; gt; gt; gt; gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:=Formula
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Jonathan Cooperquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I'm looking for a HINT. I want to figure this out myself, but I'm stuck.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I wan't to start using dynamic range names for my pivot table data. But I
gt; gt; gt; gt; gt; gt; also don't want to have to type the formula in, over and over, because I
gt; gt; gt; gt; gt; gt; create them frequently.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I've created the following macro. I'm planning for the scenario, where I
gt; gt; gt; gt; gt; gt; have more than one database in a particular file. maybe sheet 1 has the
gt; gt; gt; gt; gt; gt; first data, and sheet two has different data. My thought is to run this
gt; gt; gt; gt; gt; gt; macro on sheet one and have it default to the name DataBase1. Then when I'm
gt; gt; gt; gt; gt; gt; ready, I run the macro on sheet 2 and it would autmatically create a name of
gt; gt; gt; gt; gt; gt; DataBase2. However, each time I run the macro, it defaults to DataBase1.
gt; gt; gt; gt; gt; gt; This is where I need the first hint.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Sub Macro1()
gt; gt; gt; gt; gt; gt; '
gt; gt; gt; gt; gt; gt; ' Create a dynamic range name for my data, to be used in a pivot table.
gt; gt; gt; gt; gt; gt; ' Macro recorded 4/5/2006 by Cooper
gt; gt; gt; gt; gt; gt; '
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; '
gt; gt; gt; gt; gt; gt; Dim DataName As String
gt; gt; gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; DataName = Application.InputBox(quot;What do you want to call this range of
gt; gt; gt; gt; gt; gt; Data?quot;, quot;Name your dataquot;, quot;Databasequot; amp; cntr)
gt; gt; gt; gt; gt; gt; ActiveWorkbook.Names.Add Name:=DataName, RefersToR1C1:= _
gt; gt; gt; gt; gt; gt; quot;=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),COUNTA( Sheet1!R1))quot;
gt; gt; gt; gt; gt; gt; cntr = cntr 1
gt; gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I also realize that the formula specifically references quot;Sheet1quot;, and that
gt; gt; gt; gt; gt; gt; when I run this thing on quot;Sheet2quot;, it's going to be pointing to the wrong
gt; gt; gt; gt; gt; gt; location. I plan to tackle that next.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; thanks in advance.
gt; gt; gt; gt;

--

Dave Peterson