Automating a Pivot Table

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