Macro to adjust pivot table's date filer for last week

My only PT filter is a date filter. I need to set it to only include last week. M-F. Regardless if I run this on a tuesday or thursday, it should return the same results for the previous week. Can this be done? Also, I have my PTs to be Top 3 values, is there a way to make it stop at 3. What happens most commonly is I have 1,2,3,3,3. Is there a way to stop it at 1,2,3?

This sounds like a job for a helper column in the source data (or maybe two). One helper column will calculate whether the date is in the Mon-Fri range last week. Arrange this to evaluate to TRUE or FALSE and use it as the report filter. Another column could include rank. Do you have any criteria for breaking ties? If not and you willing to accept only the first occurrence then make a "composite key" that concatenates whatever fields are needed to make a record unique, and apply =Match(ConcatKey,ConcatCol,0)=ROW(). This evaluates to TRUE only for the first occurrence.

This explanation would make a lot more sense with a concrete example.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

P.S. you don't need VBA to pull this off.

Here is an idea of what I am wanting. Sheet 1 has a lot more columns of data but they are not relevant for this pivot.

Maybe it is easier to copy/paste the data and then remove it like I did manually but with a macro? I had help creating this macro that copy/paste values and retains formatting. Maybe I can adjust the macro to strip the excess info away?

Please Login or Register  to view this content.

OK try this:

Add a couple of helper columns:
Column C: =COUNTIFS([Error],[@Error],[Manager],[@Manager]) - this gets the number of times a combination occurs
Column D: =COUNTIFS([Error],">"&[@Error],[Manager],[@Manager]) - this "alphabetizes by rank, the errors
Column E: =[@Number]+[@Rank]/10 - this assigns a "composite based on number and rank

The Pivot table takes the top 3 Places and sorts by them. These are the top 3 errors by manager with the tie breaker being the alphabetic position of the Error.