Dynamic Pivottable range

[Niraj Baraili]

Hi Experts,

I am just trying make change the range of a Pivot table automatically. But somehow it's not working ?

Any help on this ?

  Wb1.Activate
  Sheets("Raw Data").Visible = True
  LstRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
 
 
  Set Data_Sht = Wb1.Sheets("Raw Data")
  Set Pivot_Sht = Wb1.Sheets("Summary")
  Set StartPoint = Wb1.Sheets("Raw Data").Range("A1")
  Set Datarange = Data_Sht.Range("A1:K" & LstRow)
 
  'Set Pivottable name
  PivotName = "Pivottable8"
 
  NewRange = Data_Sht.Name & "!" & Datarange.Address
 
  'Change Pivot Table Data Source Range Address
  'Pivot_Sht.PivotTables(PivotName).ChangePivotCache _
  'Wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
  With Sheet4
  .PivotTables("Pivottable8").ChangePivotCache ActiveWorkbook. _
  PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange _
  , Version:=xlPivotTableVersion14)
  .PivotTables("PivotTable8").PivotCache.Refresh
 
  Sheets("Raw Data").Visible = False
 
  End With
  end sub

 

[Chihiro]

It's because you have space in sheet name "Raw Data".

Change your "NewRange" part of the code to...

NewRange = "'" & Data_Sht.Name & "'!" & DataRange.Address

 

[Niraj Baraili]

Thank you very much Chihiro.