Help with making a cumulative timesheet automated

So you did a great thing posting a sample data set so I could play with it during lunch.

Attached you will find what you are looking for.
TimeSheet.xlsb

I added a ton of comments to the code so you can see what I am doing for each line.

Please Login or Register  to view this content.

A few notes:
1) You really don’t NEED VBA to create the aggregated data, you could just copy and paste the 12 tabs into ONE data tab
2) At this point the Pivot Table will do everything else, and if you don’t know pivot tables you NEED to learn them as they are SLIIICK and probably one of the most powerful tools in EXCEL
3) Also they are VERY easy to use, once you have standardized data like you have
4) Then I took this one step further and made a dynamic named range for you called PivotDATA
4a)PivotData counts the number of rows used in column A then counts the numbers of columns used in Row 1, and adjusts to perfectly match that, so as you increase or decrease in size it will automatically adjust
5)SO now that you have the VBA to copy and paste, the formula to dynamically adjust the pivot range, AND the pivot table you never need to do anything but enter data.
6)Impress Boss and Prosper.


If you have any questions just ask.

Cool, I find doing stuff like this helps me learn new tricks that make me better at what I do at work, especially because I have only been playing with VBA off and on for a year or so.

I commented the code pretty well but let me know if you have any questions and/or just start another thread.

So the code looks fine, so I am thinking it is the naming convention of your DATA tab.

VBA is very odd in that it thinks "DATA" is NOT the same as "Data". I had it all uppercase, and you changed the code to be NOT uppercase so the If statement was NOT skipping the DATA tab, so it copied and pasted itself. Lesson learned for you

Please Login or Register  to view this content.

Also, do you know how to walk through the code line by line with the auditing tools? Print this and read through it, it is VERY helpful.
http://www.cpearson.com/excel/DebuggingVBA.aspx

Alternatively you could do something like this:

Please Login or Register  to view this content.

It really just depends what is the cleanest or easiest way to logically break down what you are trying to accomplish

If you wanted you COULD move the DATA down one row, and adjust the macro accordingly so DATA is also blank on A1... Plenty of ways to make it more idiot proof :P And then when you find a better idiot, you just add a little more code to prevent if from happening again...

right click on the Totals tab,
Move or Copy
check on Create A Copy
select where you want the copied tab to go
click ok
rename the tab

Now for modifying the Pivot Table to play with time periods.

Click in the pivot table and if the field list is not visible right click and click on Show field list near the bottom of the popup

Now grab the DATE field and drop it into the Row Label before Project.
Then go right-click in any of the dates in the pivot table itself.
Click on Group, and because this is a Date you can do all kinds of neat groupings (Month, Quarters, Years)
Choose Month and click Okay

Now you can either leave the Date in the rows OR you can drag Date from Row Label to Report Filter.

Well I would probably add the hours on the DATA tab like you did, then you can have any or all of those fields for use in the pivot table.

You COULD do create a calcualtion in the pivot table, and it works fine but it is not quiet as easy to audit (as you dont immediately know where the data is coming from) where your method allows you to easily trace the math and such if you have any questions.

Attach your most recent file and I can take a look at what specifically you are trying to do.

Can you attach the workbook? It is easier for me to see it in the book than read about it on the forum.

The code might be pretty confusing, but I have a good gameplan to do what I'm doing. But I need to search through the j'th column for a string and if it isn't there then I'm moving to the next column to search for it. This is in case the separate columns are out of order then the program will still run in the desired way. I've looked into putting Columns(j) inside of the Range() but it never seems to work for some reason.

EDIT: I attached my worksheet so far. Let me know what you think I need to change or if you have any questions. Check TimeSheet_Dummy, ignore the others. I couldn't figure out how to delete the attachments.