+ Reply to Thread
Results 1 to 3 of 3

VBA: Selecting the last cell in PivotTable1 when there is a 2nd pivot on the same sheet

  1. #1
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Question VBA: Selecting the last cell in PivotTable1 when there is a 2nd pivot on the same sheet

    Hi All

    Hoping someone can help.
    I'm having trouble with the following piece of code. It opens the pivot details of PivotTable1 on a new sheet

    Please Login or Register  to view this content.

    PivotTable1 is the larger table underneath

    Pivots.png


    It's been working perfectly, that was until I added a second pivot on each sheet :/
    Now when I run the code sometimes it selects the total of PivotTable1 other times PivotTable2 before advancing to Selection.ShowDetail

    At first the selection seemed random but I've tested it a few times and always get exactly the same results

    To test, run the Sub BuildPivotRows().
    Add a breakpoint here, to see which cell/pivot table total is selected

    Breakpoint.png

    Results for each of the 10 SLA sheets:
    SLA1: Selects PivotTable1 total
    SLA2: Selects PivotTable2 total
    SLA3: Selects PivotTable2 total
    SLA4: Selects PivotTable1 total
    SLA5: Selects PivotTable2 total
    SLA6: Selects PivotTable1 total
    SLA7: Selects PivotTable2 total
    SLA8: Selects PivotTable1 total
    SLA9: Selects PivotTable2 total
    SLA10: Selects PivotTable1 total

    So 'show pivot detail' is correct for SLAs 1, 4, 6, 8, 10
    But gets the wrong table on SLAs 2, 3, 5, 7, 9

    Always. I can't explain it.

    Any help/advice on how I can get this sub to always select / show details for PivotTable1 would mean the world

    Many thanks in advance
    Vicky
    Last edited by vwhite; 09-03-2020 at 04:44 AM.

  2. #2
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: VBA: Selecting the last cell in PivotTable1 when there is a 2nd pivot on the same shee

    Hi All

    I worry this thread got missed as I posted it at midnight
    Any help would be greatly appreciated

    All the best
    Vicky

  3. #3
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: VBA: Selecting the last cell in PivotTable1 when there is a 2nd pivot on the same shee

    Sorry, I've got it. I don't know why I couldn't find this last night. I must have been in a blind panic

    https://www.mrexcel.com/board/thread...-total.900966/

    All sorted

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Selecting Pivot.items from cell values
    By martsanders in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2016, 08:16 AM
  2. Selecting a Pivot Table on a sheet and not caring about the name
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2015, 03:22 PM
  3. Replies: 1
    Last Post: 09-30-2015, 01:01 AM
  4. Replies: 0
    Last Post: 06-02-2015, 01:36 PM
  5. Pivot - Selecting filter based on a cell value ( not using Macro)
    By kumthekarg in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-23-2013, 02:40 PM
  6. selecting another sheet to pick up an index that links to a pivot filter
    By aldridge87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2012, 11:14 AM
  7. Replies: 0
    Last Post: 03-22-2009, 07:17 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1