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"