I have used this site a bunch over the years to figure out VB code, finally I think I have hit a wall that I cannot figure out from past posts.
I am trying to dynamically place a Pivot table. I have the code figured out to generate the pivot table but the macro will be used on varying amounts of rows and needs to have the pivot placed after the last row in column K. Right now I have it just defaulting in K47 and wish to have this value dynamically find the first blank row in "K" and skip one row and place the pivot table there.
Any help is appreciated
I am trying to dynamically place a Pivot table. I have the code figured out to generate the pivot table but the macro will be used on varying amounts of rows and needs to have the pivot placed after the last row in column K. Right now I have it just defaulting in K47 and wish to have this value dynamically find the first blank row in "K" and skip one row and place the pivot table there.
Code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Pivot!$A$1:OFFSET(Pivot!$A$1,COUNTA(Pivot!$A:$A)+1,1)", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R47C11", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(47, 11).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Fixture")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("#"), "Count of #", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of #")
.Caption = "Sum of #"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Any help is appreciated