0

I have a pivot chart which currently has the data source referring to my table.

Every week I run my code and a new row of data is appended to the bottom of my table.

The pivot does pick up this new data every week as it refers to the table, however, I want to take one less week each week So I have a years worth of data. So I want to include around rows.

Is there anyway to adjust my table to only include the years worth of rows?

Here is my sample data:

+----------+------------------+-----------------+
|   week   |      stack       |    overflow     |
+----------+------------------+-----------------+
| 12/20/17 | -142,335,432.00  | -41,641,109.88  |
| 12/27/17 | -105,428,220.20  | -47,448,990.63  |
| 1/3/18   |  -88,520,154.56  | -24,858,774.97  |
| 1/10/18  |  -42,033,431.10  |  14,573,779.35  |
| 1/17/18  |  -66,101,748.16  |  -8,670,735.22  |
| 1/24/18  |  -75,871,649.12  | -18,000,154.21  |
| 1/31/18  |  -77,027,686.63  | -11,784,198.64  |
| 2/7/18   |  -96,720,126.71  | -52,219,288.98  |
| 2/14/18  | -119,118,554.60  | -34,743,350.28  |
| 2/21/18  | -116,529,554.70  | -20,774,072.93  |
| 2/28/18  |  -86,871,998.53  | -25,993,521.20  |
| 3/7/18   |  -90,351,387.27  | -21,259,727.05  |
| 3/14/18  |  -77,968,076.28  | -51,609,924.29  |
| 3/21/18  | -120,805,352.60  | -40,338,490.97  |
| 3/28/18  |  -92,247,583.62  | -14,525,648.04  |
| 4/4/18   |  -70,821,451.36  | -35,866,864.46  |
| 4/11/18  |  -82,694,486.66  | -59,009,729.82  |
| 4/18/18  |  -79,034,094.39  | -64,231,312.42  |
| 4/25/18  |  -63,415,815.16  | -28,612,265.37  |
| 5/2/18   |  -80,372,191.96  | -53,375,611.61  |
| 5/9/18   |  -72,619,415.73  | -50,642,469.19  |
| 5/16/18  | -109,654,240.70  | -45,762,784.43  |
| 5/23/18  | -100,407,366.50  | -39,577,966.11  |
| 5/30/18  | -105,794,095.80  | -65,071,199.59  |
| 6/6/18   |  -83,630,201.98  | -60,981,969.88  |
| 6/13/18  | -104,644,821.50  | -63,754,760.71  |
| 6/20/18  |  -75,229,424.33  | -55,803,681.24  |
| 6/27/18  |  -65,237,135.62  | -54,693,832.65  |
| 7/4/18   |  -60,025,672.33  | -44,367,918.60  |
| 7/11/18  |  -30,172,175.09  | -28,392,163.28  |
| 7/18/18  |  -20,687,864.39  |  24,300,285.63  |
| 7/25/18  |  -40,476,447.03  |   4,850,881.09  |
| 8/1/18   |  -31,211,625.05  | -67,887,918.30  |
| 8/8/18   |  -29,736,938.87  | -32,905,703.80  |
| 8/15/18  |  -74,934,647.91  | -65,611,884.73  |
| 8/22/18  |  -25,220,747.20  |  -7,019,746.86  |
| 8/29/18  |  -24,608,552.13  |  -8,065,633.97  |
| 9/5/18   |  -30,119,599.95  | -26,225,633.08  |
| 9/12/18  |  -29,836,379.12  | -10,045,560.95  |
| 9/19/18  |  -61,281,567.61  | -58,427,878.27  |
| 9/26/18  |  -47,418,209.59  | -33,451,409.22  |
| 10/3/18  |  -41,321,336.46  | -25,112,764.44  |
| 10/10/18 |   -1,241,932.51  |  21,814,274.35  |
| 10/17/18 |  -19,791,273.66  | -12,199,449.75  |
| 10/24/18 |  -20,501,406.84  |   1,225,387.11  |
| 10/31/18 |  -64,116,464.30  |  -5,308,628.21  |
| 11/7/18  |  -83,657,672.02  | -19,922,992.91  |
| 11/14/18 | -112,704,007.53  | -32,939,535.69  |
| 11/21/18 |  -71,969,954.54  | -51,335,709.79  |
| 11/28/18 |  -79,668,484.56  | -67,887,918.30  |
| 12/5/18  |  -44,134,343.99  | -32,905,703.80  |
| 12/12/18 |  -71,700,079.84  | -65,611,884.73  |
| 12/19/18 |  -82,238,011.30  | -74,725,620.20  |
| 12/26/18 |  -59,385,932.41  | -54,947,256.94  |
| 1/2/19   |  -42,717,830.26  | -31,110,199.14  |
| 1/9/19   |  -11,029,444.63  |   7,309,440.90  |
+----------+------------------+-----------------+
Improve this question

2 Answers 2

Reset to default
1

Changing the source range for the pivot will be tricky as Excel does not allow non-contiguous cells to be used in pivot tables. Instead you can create the pivot by selecting entire column to account for all future entries. Then the pivot can be manipulated to show a changing range as shown in the code below. Hope that works for you.

EDIT

The code is updated below to include 50(can be changed) from the bottom.

Sub MovingPivot()
    Dim ws As Worksheet
    Dim dtTop As Date
    Dim i As Integer, n As Long

    Const NumWeeks = 50 'Change this to set weeks range


    Set ws = ActiveSheet 'Set reference to your worksheet here

    'reset the pivot filters
    ws.PivotTables("Table1").PivotFields("Date").ClearAllFilters
    'remove blank values
    ws.PivotTables("Table1").PivotFields("Date").PivotItems("(blank)").Visible = False


    'find the date entry in 50 places from bottom.
    i = 0
    For n = ws.PivotTables("Table1").RowRange.Count To 1 Step -1
        If i = NumWeeks Then
            dtTop = ws.PivotTables("Table1").RowRange.Cells(n).Value
            Exit For
        End If
        i = i + 1
    Next n

    ws.PivotTables("Table1").PivotFields("Date").PivotFilters.Add2 Type:=xlAfterOrEqualTo, Value1:=Format(dtTop, "dd-mmm-yyyy")
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

okay i like the idea, so my data source should be all the columns, in this case X:AH . And where in this code can I alter it to get from the last row up 50 rows?
yes the data source should be the entire column (assuming you heading row is on row 1). See updated code above to set 50 weeks from bottom.
Trying to do it a second time, this time the data source is not in a table, it is actually another sheet. I get unable to get the rowrange property of the PivotTable class on line For n = ActiveSheet.PivotTables("scencount").RowRange.Count To 1 Step -1
Hansraj you there?
I have updated the code. Activesheet only works on the currently active sheet. if your pivot is in another sheet use that sheet reference. in the line set ws = Activesheet, replace with set ws = thisworkbook.worksheets("nameof yoursheet")
|
0

If you want to continue using a Pivot Chart, you can use the time line slicer to include/exclude data. You'll need to adjust the time line or filter manually after the data has refreshed. Or write VBA to set the filters.

A non-vba version that does not require slicers can also be achieved with a standard chart (not a pivot chart). Create named ranges with Offset functions that grab just the rows of data that you are interested in, then plug these range names into the standard chart. When new data is added to the table, the named ranges that feed the standard chart will also be updated.

If you need a step by step, take a look at https://peltiertech.com/Excel/Charts/DynamicLast12.html

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.