Results 1 to 2 of 2
  1. #1
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62

    Pivot Table dynamic TableDestination

    Hi, my aim is to do something like this, but I have a type mismatch on LastCol so I'm trying to figure out what I need to do. Just focus on the "TableDestination:=" part. I need to place the new pivots in the last row and last column location, because I'm trying to loop and create a few pivots next to one another.



    Code:
    Dim LastRow as Long, LastCol as Long
      
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    LastCol = Cells(7, .Columns.Count).End(xlToLeft).Column
    
    wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceRange).CreatePivotTable _
    TableDestination:=Worksheets("Summary").Range(LastCol + 2 & LastRow + 5), TableName:="PivotTable1"

  2. #2
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62
    You know what, I think I got it. Simple solution but I wasn't seeing it.

    Code:
    wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceRange).CreatePivotTable _
    TableDestination:=Worksheets("Summary").Cells(LastRow + 5, LastCol), TableName:="PivotTable1"

    Just had to use .Cells instead of .Range

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

Similar Threads

  1. Pivot table
    By bbridgers05 in forum Access
    Replies: 3
    Last Post: 12-29-2018, 11:29 AM
  2. Dynamic CreatePivotTable TableDestination
    By Sephaerius in forum Modules
    Replies: 1
    Last Post: 12-07-2018, 11:18 AM
  3. Pivot table ref
    By Bobby100 in forum Queries
    Replies: 4
    Last Post: 10-28-2016, 11:08 AM
  4. Replies: 0
    Last Post: 09-22-2014, 07:07 AM
  5. Pivot Table Format
    By TPH in forum Access
    Replies: 1
    Last Post: 01-03-2012, 11:59 AM

Posting Permissions

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