refresh pivot data with visible data

[kcube17]

Hi I am new to excel forum. I have recorded a macro. in between there is a pivot table which needs to be daily refreshed with all visible cell. The number of rows and colums will change.

is there any way to refresh the pivot with all visible cells

ActiveSheet.PivotTables("PivotTable4").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C:\Users\kkk\Desktop\[Jan5.xlsx]Sheet1!R1C1:R179C34" _
, Version:=xlPivotTableVersion12)

as in the code the source is from R1C1:R179C34. is there any way to select only visible cells.

[amalivijay]

Hi,

run the below macro

instead of dashboard you enter the sheetname of pivot tsble sheet and above code in sheet1 you have the data right.suppose you change the sheet name of data sheet , change the sheet name in codeing also.

Sub UpdatePivot()
Application.ScreenUpdating = False

Sheets("sheet1").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
With ActiveWorkbook.Names("PivotData")
.Name = "PivotData"
.RefersToR1C1 = Selection
.Comment = ""
End With
ActiveWorkbook.RefreshAll
Worksheets("Dashboard").Select
Application.ScreenUpdating = True
End Sub

Vijay

[amalivijay]

Hi,

Here is the macro for refresh the data
Note: rename the sheetname in first line and last line (for "data" you can put your data sheet name and for "dashboard" you put your pivot sheet name


Sub UpdatePivot()
Application.ScreenUpdating = False

Sheets("Data").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
With ActiveWorkbook.Names("PivotData")
.Name = "PivotData"
.RefersToR1C1 = Selection
.Comment = ""
End With
ActiveWorkbook.RefreshAll
Worksheets("Dashboard").Select
Application.ScreenUpdating = True
End Sub

[kcube17]

thanks a lot