I would like to modify the below code to calculate weekly ad monthly totals. the code currently calculates 2013 total (by year)
Please Login or Register to view this content.
Please Login or Register to view this content.
I would like to modify the below code to calculate weekly ad monthly totals. the code currently calculates 2013 total (by year)
Please Login or Register to view this content.
Please Login or Register to view this content.
Hi..
Can you attach a sample Workbook..
Just quickly though..
Couldn't all this...
Please Login or Register to view this content.
Be replaced by something like..
Please Login or Register to view this content.
Here is the attached sample workbook. I just need the syntax to calculate weekly and monthy. My code works but it's calculating all the worslheets that are hidden and current. Please advise
No worries.. its 1.00 am here.. so i will look tomorrow.. but please take my advice on repetitive code though (I am guilty as anyone sometimes)..
Less code = less confusion.... thats the goal anyway...
A few questions.. as your workbook is a bit confusing..
The Worksheets that are named for example "20131013" .. are they a weekly sheet where you enter the data for that week?
Assuming that..
I can see how you want to get your yearly values (if first 4 characters of sheet name is 2013 as an example..).. so if you want to make it so you can get weekly running totals (for current week) and monthly running totals (for current month).. I would suggest the following:
Name your sheets like yyyymmww. (yyyy = year, mm = month, ww = current week number).. So this weeks sheet would be 20131042
That way you will be able to get data from specific sheets depending on those values using the Right function..
Having said all that.. if it were me.. i would just have 1 Worksheet where you enter all data. Add a Date column where you add the date for each entry.
Then use autofilter to get the data you need (this weeks, this months, whatever) and Sum the visible rows after Autofilter using SpecialCells(12) to populate your Weekly/Monthly/Yearly running total tables..
You can use 2 Date pickers to set the Date Criteria for autofilter (this way you can have totally custom periods of reporting).. or just have buttons like (This Week, Last Week, This Month etc etc)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks