Results 1 to 2 of 2

Dynamic CreatePivotTable TableDestination

  1. #1
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    48

    Dynamic CreatePivotTable TableDestination

    I feel like this should be pretty straightforward, so I don't know what I'm missing here. All I'm trying to do is rather than explicitly stating:
    Code:
    .CreatePivotTable TableDestination:="Worksheet!R1C1",
    I want it to be dynamic, something like:

    Code:
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    .CreatePivotTable TableDestination:=Range("A" & LastRow + 1),
    The reason why is because I'm creating up to 15 pivot tables, but some of them might not exist on some reporting days. So rather than having large gaps on the worksheet (such as defining 15 hardcoded spots for each pivot and only 8 of them populate), I want to dynamically fill in the pivot tables 1 row beneath the previous one going down column A.

    Is this doable? It's like I'm halfway there, but the error is stating that a pivot table cannot overlap another pivot, so perhaps it isn't returning the correct LastRow?

  2. #2
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    48
    Disregard, it does work. Writing it as this does the trick:
    Code:
    TableDestination:=Worksheets("Sheetname").Range("A" & LastRow + 1)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-06-2016, 12:18 PM
  2. Dynamic textboxes
    By braddy in forum Database Design
    Replies: 2
    Last Post: 11-15-2013, 01:16 AM
  3. Dynamic Default Value
    By okason in forum Access
    Replies: 1
    Last Post: 03-21-2012, 02:36 PM
  4. Dynamic Form, Dynamic labels/ captions?
    By JFo in forum Programming
    Replies: 15
    Last Post: 10-12-2011, 07:33 PM
  5. Dynamic Data
    By top1hat19 in forum Access
    Replies: 4
    Last Post: 02-14-2011, 05:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums