Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Pivot Tables

  1. #1
    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

    Greetings all
    I have a procedure that creates a pivot table. When new data is added the procedure deletes the old table and creates a new one that includes the new data. The sheet number is incremented up each time IE.
    Sheet(148) Any way to turn back the clock on this? I have tried renaming the sheet in the properties window but the next time the procedure runs it's back up to what it was plus one.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not change your procedure to include RefreshTable when new data is added eg

    ActiveSheet.PivotTables("PivotTable2").RefreshTable

  3. #3
    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

    That will work if the existing data is changed but when you add data to the bottom of the input area it doesn't see it.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    George - I keep the original sheet (it's renamed to something more suitable) clear only the data and use this simple routine to determine the last row in the data and substitute the variable RESP in the range for the pivot table. This way the pivot table no doesn't keep changing.

    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Select
    RESP = ActiveCell.Row()


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

    Default

    Hi George


    You should use a Dynamic range, this way you can add and delete from the range as needed and It will expand/contract Up/down Left/Right accordingly

    I have many examples here

    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
  •