Refresh Pivot script

[jei eusebio]

Hi Ninjas,

I used this vba code and made a button for it to clear columns, i would just like to add a refresh pivot option to the command so that it would also refresh the pivot in the "Pivot" sheet. Thanks in advance guys!

Sub Clear_Stuff()
Dim lngLastRow As Long

With Worksheets("Raw")
lngLastRow = .Cells(.Rows.Count, 3).End(xlUp).Row
.Range("A:J").Clear
End With

With Worksheets("Main")
lngLastRow = .Cells(.Rows.Count, 3).End(xlUp).Row
.Range("B:E").Cells.ClearContents
End With

With Worksheets("Pivot")
lngLastRow = .Cells(.Rows.Count, 6).End(xlUp).Row
.Range("A4:H" & lngLastRow).Cells.ClearContents
End With

End Sub

 

[Somendra Misra]

Hi,

I don't know why you are calculating last row in first two With statement, when you are actually clearing the whole columns.

Try below statement:

Sheets("Pivot").PivotTables("PivotTable2").PivotCache.Refresh

Change the Pivot Table name from PivotTable2 to your name.

Regards,