Pivot Tables - Refreshing with data??.....

  • Thread starter Thread starter MrAlMackay
  • Start date Start date
M

MrAlMackay

Is it possible to do either of the following in either VBA or alternatively
through excel automatically?:

1) Automatically make a pivot table recognise when a row has been added
directly underneath the existing data and extend the selection to include any
additional rows

2) Have a macro automatically create a pivot table and automatically select
the range of data (which will be different all the time), or alternatively
present a dialog box that justs asks the user to select the range to make the
pivot table on and then create the rest of the pivot table automatically.

Appreciate any help that anyone can offer on this as I'm completely lost on
this.

Cheers, Al Mackay ([email protected])
 
Insert=>Name=>Define

Name: Database
RefersTo: =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),10)

the actual pivot table won't update until you actually do a refresh.



then when you set up your pivot table set the source to be Database

Turn on the macro recorder to get the code to create a pivotTable.

to get the range dynamically, assume the data table starts in A1

set rng = Worksheets("Sheet1").Range("A1").CurrentRegion
 
Back
Top