Need Help Condensing Code

**bchilme** · 01-09-2015, 01:18 PM

Hi All! So i have code that does exactly what i want but it is too large to run. Would anyone have any suggestions on how i can condense this to allow it to run? When it looks at Target.Address E32 i will have 54 blocks of that "Case 4 Week Rolling...." code so it gets rather large, where the ****** are. The Data is in the form of a calendar with columns being every day of the year so i dont think setting up a pivot table and utilizing slicers will allow for what this code does. It basically removes all the data fields in the pivot table then adds in calculated columns i have worked out after the calendar. Any help or inspiration is appreciated!

Please Login or Register  to view this content.

**protonLeah** · 01-09-2015, 02:34 PM

thanks, code tags inserted

**bchilme** · 01-09-2015, 02:37 PM

Sorry about that. Updated.

**protonLeah** · 01-09-2015, 03:30 PM

What exactly is in cell E32? Is it the actual text string: "4 Week Rolling Ending on 01/24/2015"? How does it get changed, manually, week to week? Is there a cell with the current date somewhere in the sheet?

**bchilme** · 01-09-2015, 03:50 PM

Correct, it is a Data Validation List. The user selects which week they want to see the data for. So for example in PivotTable 10 WEEK ENDING ON 1/24/15 would be the fourth week of the year. So for the 4 week rolling we would need the data from weeks 1-4. So the code clears the pivot table then puts "Week 1,2,3,4 All Assignments" into the pivot and then puts "Week 1,2,3,4 Days Worked". Then i have a cell outside the pivot that just divides the sum of assignments by the sum of days worked and i graph off of these numbers.

If you notice for pivot table 11 i calculated the numbers in to helper columns in the data. So rather than adding 8 data fields to the pivot i add only 2. I suppose i could do that for pivot table 10 but its alot of work so just seeing if i can keep this somehow.

**protonLeah** · 01-09-2015, 10:02 PM

The code below uses a loop which starts four weeks prior to the current week. Not tested since no workbook was posted.

Please Login or Register  to view this content.

**bchilme** · 01-16-2015, 12:45 PM

Thanks for the reply it worked perfectly! Did some minor changes but worked awesome. Thanks!

Please Login or Register  to view this content.

Need Help Condensing Code

LinkBack

Thread Tools

Rate This Thread

Display

Need Help Condensing Code

Re: Need Help Condensing Code

Re: Need Help Condensing Code

Re: Need Help Condensing Code

Re: Need Help Condensing Code

Re: Need Help Condensing Code

Re: Need Help Condensing Code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Condensing VBA Code

Condensing Find Code

condensing vba code

Need help condensing with-end with code

Trouble Condensing Code

Bookmarks

Bookmarks

Posting Permissions