I am using a range to create pivot tables. Unfortunately the length of the sheet changes hence the range and therefore "sourcedata" changes.
how can i tell excel to use the new range everytime using the r1c1 style.
Code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R2741C63", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("COUNT = 6")
.Orientation = xlRowField
.Position = 1
End With
how can i tell excel to use the new range everytime using the r1c1 style.