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.