Automatically update date range in Pivot Table Filter

[Annie]

hi there,

I'm working on a report that update appointment created for the week, month and year to date. The data is from an outside source. After the source updated every week, I have to go to each pivot table to untick and tick the working days for previous week, month to date and year to date. Is there any VBA code to make this process automatic? I tried to upload the file, but the size appears to be too big to upload.

Thank you in advance!
Annie

 

[Abdul Matheen]

Hi Annie,

Try the below VBA code into your workbook, this code will automatically check the last row of the data and reset the pivot range for all the pivot in activesheet. In the below code i have highlighted the code has be change as per your pivot and datasheet.


Sub t()

Dim PC As PivotTable
lr = Sheets("Data").Range("a1").End(xlDown).Row
For Each PC In ActiveSheet.PivotTables
PC.SourceData = "Data!R1C1:R" & lr & "C5"
Next PC
End Sub

If you getting any error revert me back i will assist on the same.

dont forget to click "LIKE" for this response.

Regards
Abdul Matheen

 

[Annie]

Thank you Abdul to get back to me.

I'm sorry. I'm very new to VBA and I don't really know how to update PC.SourceData = "Data!R1C1:R" & lr & "C5". Is there anywhere to send you the file to take a look? It's too large to upload.

 

[Abdul Matheen]

Yes Annie you send me the file, remove some data from the data sheet just keep 100 rows data and send that time it will less.

Regards
Abdul Matheen

 

[Annie]

Hi Abdul, please find attached file and I left what I wanted to do on the top of 2 pivot table. Let me know if you have any question. Thank you for your time!

 

[Abdul Matheen]

Hi Annie,

Find the attached file, i have create a macro that will change the dates automatically. In this workbook i have given one cell which user has to enter the beginning date of the week and then click on "Refresh" Button then the macro create the week date for the pivot.

There is one more macro which you have to run only when you upload the new data. Just upload the new data into data sheet and then press ALT+F8 it which opens a macro window, select the "pvtdatareset" macro and click on the run button, it will reset the pivot source range with the new range.

Revert if you have any questions.

Regards
Abdul Matheen

 

[Annie]

Thank you so much Abdul. I will let you know if there's question.