Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Pivot Table Updates...Very easy

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

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




Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •