Pivot Table Updates...Very easy

[knowfun]

Q: How do I update my pivot tables after I have added some new data to the data source sheet? When I hit refresh, it does not grab the new data I just entered. HELP!

 

[Asala42]

Hiya

I'm guessing the problem is from one of 2 things.

1: Try refreshing by right-clicking on the pivot table and then choose refresh.

2: Your new data could be outside the range the pivot table is looking (new data rows under the specified data range for example). You could go back into the pivot table wizard and select a bigger range than you need to cover new data. -this should create a new variable (called "(blank)") that you can just right-clickhide.

Hope that works
Adam

 

[knowfun]

This is great stuff...thanks a million. It worked like a charm (data range in the wizard). THX.

 

[Mark W.]

On 2002-03-28 14:25, Asala42 wrote:
Hiya

I'm guessing the problem is from one of 2 things.

1: Try refreshing by right-clicking on the pivot table and then choose refresh.

2: Your new data could be outside the range the pivot table is looking (new data rows under the specified data range for example). You could go back into the pivot table wizard and select a bigger range than you need to cover new data. -this should create a new variable (called "(blank)") that you can just right-clickhide.

Hope that works
Adam

Adam, I wouldn't recommend your 2nd option for a couple of reasons...

1. Leaving blank cells in the range deprives the user of the effective use of item Grouping -- a very powerful summary capability.

2. What's a sufficient number of rows? Eventually you'll be re-editing the cell range entered as the PivotTable's data source anyway.

A much better approach is to name the data list cell range (e.g., Database), enter that name as the PivotTable's data source, and as new data is added to the list just modify the cell reference associated with the range name.
This message was edited by Mark W. on 2002-03-28 15:07

 

[lenze]

Following up on Mark's suggestion, if you name your data table "database" and use the built in Data Form to enter and/or edit the table, Excel will update the range automatically to include new rows. You might also do a search for "Dynamic Named Ranges". They work great with Pivot Tables

 

[GeorgeB]

Sounds great.
How would this be incorporated into the following code

Public Sub ByDesc()
Application.ScreenUpdating = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("By Desc").Delete
Sheets("By Desc Chart").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets("INPUT").Select
Range("A60").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Range("A60").CurrentRegion.Address).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="NAME", _
ColumnFields:="M", PageFields:="DESC"
ActiveSheet.PivotTables("PivotTable3").PivotFields("AMOUNT").Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable3").Format xlTable2
ActiveSheet.Name = "By Desc"
Sheets("By Desc").Move After:=Sheets(3)
Range("B5:M5").Select
Selection.ColumnWidth = 7
Range("A1").Select
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.Orientation = xlLandscape
.LeftFooter = "&B Confidential&B"
.CenterFooter = "&D"
.RightFooter = "Page &P"
End With
Range("A7").Select
Do
Range(ActiveCell, ActiveCell.Offset(0, 13)).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(3, 0).Select
Loop Until ActiveCell.Value = ""
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("By Desc").Range("A1")
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveSheet.Name = "By Desc Chart"
Application.CommandBars("PivotTable").Visible = False
With ActiveChart.PageSetup
.LeftFooter = "&B Confidential&B"
.CenterFooter = "&D"
.RightFooter = "Page &P"
End With

End Sub

 

[Asala42]

Mark W.

Very good point. The named range approach is quick, easy, and efficient. I'm kicking myself for years of pivot tables the other way.

Good call
Adam

 

[Dave Hawley]

Hi

If you go with lenze's approach you wont even have to update the range. See:
http://www.ozgrid.com/Excel/DynamicRanges.htm