I have a spreadsheet that I import different amounts of data to each
day, overwriting the previous days each time.
I want to automate the production of a Pivot Table based on the data in
the spreadsheet.
I know I can use the PivotTableWizard which refers to a named range
"Database" if it exists.

I have the following code:-

Public Sub SetNameRange()

Worksheets("Data").Activate
ActiveSheet.UsedRange.Select

Sheets("Data").Names.Add Name:="Database",
RefersTo:=ActiveSheet.UsedRange.Select

End Sub

This selects the used range but will not add the name to the
spreadsheet. Does anyone have any idea where i am going wrong?

You could change the code to:

Public Sub SetNameRange()
ActiveWorkbook.Names.Add Name:="Database", _
RefersTo:=Worksheets("Data").UsedRange
End Sub


Droidy wrote:
I have a spreadsheet that I import different amounts of data to each
day, overwriting the previous days each time.
I want to automate the production of a Pivot Table based on the data in
the spreadsheet.
I know I can use the PivotTableWizard which refers to a named range
"Database" if it exists.

I have the following code:-

Public Sub SetNameRange()

Worksheets("Data").Activate
ActiveSheet.UsedRange.Select

Sheets("Data").Names.Add Name:="Database",
RefersTo:=ActiveSheet.UsedRange.Select

End Sub

This selects the used range but will not add the name to the
spreadsheet. Does anyone have any idea where i am going wrong?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html