VBA - Create weekly & Monthly wise summary : weekly changes shown in another sheet.

[prasanna]

My Dear experts,

Good Day

I need one big help... I m preparing weekly reports which eating me lot of time doing copy and pasting each week of employee count and category.

I need help to create a VBA codes for this report to generate on weekly basis.

1. Automatically add employee counts based on category in weekly wise , week1, week2, week3 , week4 and week5 till December ( Sample table created as Weekly_Summary)

2. Automatically add employee counts based on category in Month wise , Jan, Feb , Mar till December average count of all 3 months ( Sample table created as Montl_Quarter_Summary)

If anything changes in the any sheet week1 , 2 , 3 or 4 at any cell it should copy of that whole row is put to "Changes Sheet Result" and flagged Yellow and New entries flagged Green ?

I have attached sample employee numbers count.. there are more than 2800 people working in our team.

I need full support from this forum to create this report, i m spending lot of time to prepare on weekly basis doing copy paste on each week and added 2800 lines on each week. I dont want to do this.

Once week completed automatically copy and paste to new week sheet with next week number.

Let me know if any more information required

With regards
PS

 

[vletm]

@prasanna - for precheck
1) Press [Fill Sheet]-btn - It's ready to fill all rows (2 to 49).
If there is valid sheet for every of those rows.
Valid sheet is like 'left(Month,3) & " Week" & Week'
2) I make 'minor' modifications to You formulas.
'3') If anything changes in the any sheet week1 , 2 , 3 or 4 at any cell it should copy of that whole row is put to "Changes Sheet Result" and flagged Yellow and New entries flagged Green ?
This needs 'a little more details of vision'
If anything changes in any sheet week... Copy is Okay,
but if You want different colors then somewhere have to be information of before change. ... Do You have an idea to have copies of all sheets too?
Tell few more word about those colors too ... where do want those highlights?
>> Ideas ... Questions ... Answers?

 

[prasanna]

HI Vletm,

Thanks for reply,

If anything changes in any sheet week... Copy is Okay
Answer is Yes,

Formulas is correct, i need january month entry we are considereing only last week entry what ever is there for month calculation.

but if You want different colors then somewhere have to be information of before change. ... Do You have an idea to have copies of all sheets too?

Answer : The information is available in Week1, 2, and 3 and if any changes happen in any cell its moved to Changes sheet result ( Flag in yellow). This keep on going every week till December I need all the changes data in changes sheet result.

and also any new entry added to Jan Week4( Current week ) it should copy and paste to changes sheet result ( Green )

One more important point..

VBA to create automatically next week sheet for example we are in Jan last week if i click add new week button automatically its should copy all current week data in to new sheet except support core.

Thanks
PS

 

[vletm]

@prasanna ...
1) Now, there are '4 weeks sheets'.
How many 'week sheets' will be there in the end of year? (4 or 48)
2) Monthly_Quarter_Summary -formulas were still wrong!
2a) Quarterly - Report gives NOW better value.
2b) Monthly - Report gives NOW only the last weeks of month value.
3) Compare - Copy - Paste - Colors
Every 'Week sheet' from 'Jan Week1' to 'Dec Week3'
every 'row' from 2 to last row of WeekA
every 'Jan Week1' row compare to 'Jan Week1's next sheet' same row
If changes in any cell of same row then
which week's row copy to 'Changes Sheet Results' and
which cells do want highlight?
Could You make sample marks with those data?
Question/Idea:
Why do You need so many 'Week'-sheets? One sheet could be enough!
Of course then this needs one more column, which tells weeks number ...
Why do You copy 48 times almost same data? Now, there are 4*~407 rows.
There will be many 'doubles' in the end of the year (12 *~407)?
If You could copy only changed rows to one year-sheet,
(previous original rows could save to 'Changes Sheet Results'),
then there would be only ~407 rows in the end of year...?

 

[prasanna]

Wow Vletm,

Your idea is superb, only one sheet its amazing, I thought this is impossible, now you are making this possible.
You can add any number of columns no problem, but I don't want to do weekly wise copy and paste the data and creating new week sheet.
Can u please proceeds your idea that is very profitable and huge time and data savings.
Now the below points I m looking for...
Weekly total number of employees count based on categories, but importantly I want to see previous weeks and monthly Number in summary sheet.
Weekly total changes in changes sheet with all the employee details ( Copy and paste ) any cell change from the main sheet.
Please highlight any new employee addition to the team ( I mean new employee join) highlight in green color
For any change in any cell its in Yellow color ( Both this rows copy and paste to Change sheet)

--- This data is sample I have added only 406 employees details there are total 2800 employees in my team.


I hope there is no confuse.

I have a confident in this forum and you, definitely you will give me a big surprise.

I am eagerly waiting for your shout.

With regards
PS

 

[vletm]

@prasanna - preview, for getting ideas
New version .. Try to test very careful ... I didn't check everything and so on ...
1) I've done weeks 1&2, those sheets are still there.
2) You should copy 'new data' to 'New Week'-sheet (now, You can use 'Jan Week3' like 'new data'). How would You copy 'in the real life that data'?
3) Go To 'Weekly_Summary'-sheet and press [Fill Sheet]
4) Critical! You have to write 'new data week's the 1st Monday's date'
5) There are few checkings ...
6a) The code will 'fill' Weekly_Summary's named row
6b) Compares 'New Week' >< 'WeekS' (this is the most previous data!)
6c) Make some colors if needed and copies to 'Changes Sheet Result'
6d) Moves 'New Week' to 'WeekS'
6e) Gives "DONE"
--- still missing at least: Set 'Last Weeks' in 'Montl_Quarter_Summary'-sheet ...
++ 'Montl_Quarter_Summary'!Cell[A1] is important date! It names current year!
Btw. why this uses from 'Jan' to 'Dec', not previous 12 months?
... maybe some 'minor' things have to tell later? but, ask
>>> I gotta rest now and I'll continue after You good feedback <<<

 

[prasanna]

Thank you vletm,

First you take rest , after that you can start with this dashboard.

Going wonderfully, I don't have enough words to explain you, you are a champion...

Now you know the ideas of this dashboard.. I will wait for next version.

For your question, We are using from Jan to Dec because our company FY ( Financial year is starts from Jan to Dec).

Take good rest and good sleep. Have a nice evening.

Thanks
PS

 

[vletm]

@prasanna
Q1) Dashboard - 'Montl_Quarter_Summary' are those same?
Q2) FY ... if You would have two years monthly calendar, then You could see previous years information too ... no need to take care mystery 31/12 -> 01/01 change never. Just continue using...? and You could see something in every January too (not empty table!)
Q3) Same way, 'Weekly Summary' could continue ... over and over?
Q4) Is it easier or useful to select 'adding week' by selecting row from 'Weekly Summary'-sheet? ... and You noticed that there are every week of year now; not 4*12.
Q5) How about 'automatic backup' with every opening of file?
Q6) Did You tried to 'add new week data'? Any challenges?

 

[prasanna]

Hi Vletm,

1. Monthly _Quarter Summary is ok its same.

2. I don't want previous year data. I need the data capture from Jan 2016 to going on another two years.

3. This is also same I need only data capture from Jan 2016 to going on another two years.

4. Yes correct but this one I m little bit confuse , do on your own idea Vletm , I am not that much good in analytics.

5. Previously I m keeping in the same file, but if you are doing automation you need to help me to keep backup its very fair idea.

6. I have tried , if I click fill sheet button its going to last row of and shows Error 400 message box.

let me know if any more information required.

Waiting for your shout.!!!!!

Regards,
Prasanna

 

[vletm]

@prasanna
1..4) Gotta check what is the best for You
5) You would get 'backups' too.
6) Hmm... my #6 Reply has instructions ...
6a) Did You copied data to 'New Week'? There have to be data!
You can copy 'Jan Week3'-sheets data to 'New Week'
6b) Which date did You write?
6c) Did You get 'Do You want to start?' ?
6d) Then error occurs, It shows some code and the place of error.
You should take a screenshot form that for me.
After that, it's possible to find out 'error'.
Q) How do You 'normally' get new data for this?

 

[prasanna]

Hi Vletm, Good evening,
How are you..??

1..4) Gotta check what is the best for You
The best is i need only single sheet as a base.( I think this is the best) if this is not possible, click and create new week sheet automatically with current week data.

5) You would get 'backups' too.
Yes on weekly wise in separate worksheet with one separate file

6) Hmm... my #6 Reply has instructions ...
PFB

6a) Did You copied data to 'New Week'? There have to be data!
You can copy 'Jan Week3'-sheets data to 'New Week'

I am unable to copy the data screen shot attached
Error Screen shot when I clicked Filled sheet button it shows error msg and also its not copying to new week
If I select today's date or any Monday's it will go directly to last row of Weekly summary column


6b) Which date did You write?

Yes ( Tried all Previous Monday's date and future Monday's date including today as well)

6c) Did You get 'Do You want to start?' ?

Yes ( This is very nice)

6d) Then error occurs, It shows some code and the place of error.
You should take a screenshot form that for me.
After that, it's possible to find out 'error'.

Screen shot attached in the file.

Q) How do You 'normally' get new data for this?

I am doing copy and paste each week in each sheet except Support core category.

Let me know if any more information required.

Thanks
Prasanna

 

[vletm]

@prasanna - I don't give up
I copied (from 'Jan Week3'-data to 'New Week') ready for You.
Please, go to 'Weekly Summary'-sheet and press [Fill Sheet]-btn.
Date is 18/01/2016.
If still error comes, please take large screenshot.
I need to see that code sheet too, especially where is that yellow highlight row.
You can solve this, Okay!

 

[prasanna]

Vletm,

The same screen error its coming up Error 400 which I sent earlier.

Its taking to VBA coding sheet but in that area its completely blank.
PFA file ( Screenshot )

Regards,
Prasanna

 

[vletm]

@prasanna - something ... like challenge
but 'Montl_Quarter_Summary'-sheet looks in next new version like this.

I made a test version for You:
There will come hopefully many message boxes like number and [OK]
Please write down which numbers You'll get.
Instructions:
1) open that file ( .xlsm)
2) go to 'Weekly Summary'-sheet
3) click [Fill Sheet]-button
4) write date 18/01/2016
5) follow with instructions ...
If error comes then SAVE that file at once and send it back to me.
Those 'extra message-boxes' hopeful shows where do You get error.

 

[vletm]

@prasanna
I'm still thinking this Your message ...
Error Screen shot when I clicked Filled sheet button it shows error msg and also its not copying to new week
If I select today's date or any Monday's it will go directly to last row of Weekly summary column
1st You clicked [Fill Sheet], write 18/01/2016 for date?
What do You mean 'I select today's date or any Monday's'?
How did You select 'today's date'?
If date is not 'valid date' then nothing happens!
Please, check how do You see 'dates' in a-column comments.
Move You mouse over any of those Months (January ... December) and You will see 'valid dates'. Do those dates look good? like day/month/year
Cell[A5] has 25/01/2016.
>> Here, no error messages!
I'll wait those my previous Reply's results.
I'll have still few ideas, how to find out which makes that 'error'.
Be patient.
ps. Germany ... or are You in any country which use that language?
Because some months were written non-english.

 

[prasanna]

Hi Vletm,

Sure ... I will wait no problem..

I have found following messages

If I select 18/1/2016
Number starts at msg box 1, 2, 3, & 4 and msg box "do you want to start?
IF I press Yes
Display number is 4 than 5 and 6 two times if i press ok
after that it running only 9, 10 11 numbers

Date format we are using US date format mm/dd/yyyy

Now I m not getting any error.
Correct me if I m wrong, I found out where that error comes from before clicking the fill sheet button I have clicked empty button at new week sheet.

New version its looking amazingly... I m going to be mad and I have decided I want to learn morethings from you from this forum, I love this forum, really...

Let me know if any more information required.

Thanks
Prasanna

 

[vletm]

@prasanna
Hmm ... dates ... dates ...
Different date format could make interesting (challenge) things!
There should come more that 11 numbers.
So so, I took message numbers 1-11 away and
after that there should come few more text-messages.
You should test more. Now date will be 25/01/2016.
... someway I could be 01/25/2016 but test 'day/month/year' first!
This should OVERWRITE data, no problem!
You should get "DONE"-message then everything has done!
Ouch! You wrote that You didn't follow my instructions ...
Please, it's more challenge to find 'errors' if not follow instructions.
Why did You press that [Empty]-button?
I asked only press [Fill Sheet]-button.
Anyway, that [Empty] won't make error neither, hmmm?

This is the most newest version...
there are few modifications ... and
after any modification, there have to make full test!
Like:'Montl_Quarter_Summary' has two 'buttons'.

Now, 2015 & 2016, activate 2015 ... You will see something and deactivate 2016 ... You will see something (if those 'years' are bolded then You'll see those years values, if both non bolded then both are unvisible!) Those charts are just samples. More later.)

If You notice any 'mysteries', please let me know,
with as good information as possible.
I need Your help that You could do this working better.

 

[prasanna]

My dear Vletm,

I have observed and tested all the sheets its running fine now.

Its going splendidly.. If I showed this to my Manager I will get promotion its 100% .

We are suffered from last one 1/2 year to this automation. I have a complete confident on you...

Definitely I will inform you when I found any doubt.

Eagerly waiting for another version..

Let me know if any more information / help required.


With regards,
Prasanna

 

[vletm]

@prasanna
I didn't change nothing for that version ... hmm?
Did You really got "DONE"?
This version has few more new things:
backup - in the first time it asks permit to do 'BACKUP'-folder and after that it automatic makes backups (30 versions) with every opening of this file.
I tested forecast with Weekly_Summary. If there would be 'real data' in prev year, it would look better with 'Montl_Quarter_Summary' too. Charts are large now, but with lines ... You'll know what would be good for You.
>> There will be at least ONE BIG tests still left.
How do this work with the real data (~2800rows)?
Same time, it'll see how to work with this ... but this test have to do right way!
And You should learn to work 'right way with this too'.
>>> Ideas ... Questions?

 

[prasanna]

Hi Veltm, Good evening,

Sorry for the Delay, Just I came from office. Lot of meetings today.

Instead of Line for charts , I would suggest bar graph looks good.
Old data I have but that columns heading is different, Our manager has changed the completely new from January 2016.

How you are calculating the forecast weekly summary....??
there are minimum 25-30 people resigned from the company on weekly basis that one we have named as Support care.
there are minimum 10-15 people joining as New joinees on weekly basis.
I will check how the real data works for 2800 rows.

But I suggest I don't think to put previous year data, this is good if we have the old data to compare.

Instead of putting year data we can show up quarter wise charts (Bar graphs ) instead of year 2015 & 2016 and weekly summary on next graph.

Which sheet are you considering the main sheet which data pulls for weekly summary..?, I have tried to find out , but I have not success.

The below code shows your name, shall I put my name instead of your name as username...?
If Application.UserName = "VLEtm" Then

What is the difference between sheet "WeekS_BK" and Sheet "WeekS"


Meanwhile can you please send me the new version...
It would be very helpful.

Let me know if any more information required.

Thanks
Prasanna

 

[vletm]

@prasanna
1) Charts: those are possible to change as You need. If You do it Yourself ... be careful, there are some code for those too.
2) Forecast testing: with 'normal' forecast-function. Previous Year data would help a lot. You can see formulas in 'Weekly_Summary'-sheet. As I wrote, it's only test!
3) Just remember. You wanted that NEW employees will mark with 'green'. How about employees which don't work anymore?
4) This would be better with 2015 data too. You could see 'better' forecasting too. There are places for 2015-data.
5) You wrote something about 'Which sheet are you considering the main sheet which data pulls for weekly summary..?' Hmm?
If this means, how to use this file ... I try one more time...
You have somewhere 'week-data'. It have to look like 'WeekS'-sheet.
You copy from Cell[A2] to the lastrows column[N] and
Now --- very careful --- select 'New Week'-sheet and activate cell[A2] and paste.
Select 'Weekly_Summary'-sheet and press [Fill Sheet]-button.
>> I could change that 'Date' question to selecting wanted row from 'Weekly-Summary'-sheet BEFORE pressing [Fill Sheet]. How do You think?
6) Do not change my 'VLEtm', it's for testing purpose only. I will take it away one day; then this is as ready as need.
7) "WeekS_BK" - "WeekS":
>'WeekS_BK' is one more step older 'WeekS', I did it for like 'Undo'-function.
... if You notice that WeekS is totally wrong (just after run [Fill Sheet]) it's possible to 'undo' that [Fill Sheet]. Only 'Changes Sheet Result' gotta 'undo' manually.
>'WeekS' is the previous ( the newest data ).
Then You press [Fill Sheet], the 'New Week'-data will compare with 'WeekS'.
8) What do You need for new version?
Chart: Line to Bar?
Weekly_Summary 'week selection' with row?
Or what?

 

[vletm]

@prasanna
I changed:
> charts lines to bars.
> 'Weekly_Summary' week selection --- You gotta select VALID Week BEFORE press [Fill Sheet]
Ideas ... Questions?

 

[prasanna]

Hi Vletm, good morning...How are you...??

Please find below ,

3) Just remember. You wanted that NEW employees will mark with 'GREEN'. How about employees which don't work anymore?
**Previously we have categerized as Resigned, now for data confidentiality at our team we have changed that category name to support core. ( Those who are support core that employee is has released from our company permanently). you can mark that employees as RED ( I totally forgot about this category). Thanks Vletm
Please note : There are people serving notice period as also called support core and Moved from the company also Support core category, once they released from the company that entire row moved to changes sheet as RED.

>> I could change that 'Date' question to selecting wanted row from 'Weekly-Summary'-sheet BEFORE pressing [Fill Sheet]. How do You think?
**Its good idea, if you try something like Week Selection FROM week number (Date or month) and TO (Date or month) and press fill button or generate numbers.

8) What do You need for new version?
**Its same as new or updated file which you are working.

Weekly_Summary 'week selection' with row?
Or what?
**Week summary.

New version file,
> 'Weekly_Summary' week selection --- You gotta select VALID Week BEFORE press [Fill Sheet]

Q. From where I want to select Week..? I m not getting any popup or drop down.... Please suggest


I appreciate your patience, dashboard is progressing very nice. Let me know if any more information required.


Thanks
Prasanna

 

[vletm]

@prasanna
1) 'not work anymore': Now 'Fill Sheet' compares 'New Week' to 'WeekS' and tries to find differences. So 'not work anymore'-category cannot find that way. So ... with previous loop, it have to make 'mark stamp' all row that has checked and after that all non 'mark stamped' can copy as 'Not work anymore' ... Okay?
All other changes will be 'just normal changes'.
Gotta make plan how to do this... so not ready.

2) 'Weekly_Summary' week selection:
2a) open that sheet
2b) there is 'Week'-column
2c) activate wanted week cell like '16-4' (Cell[B58])
2d) Now You have selected 'wanted week! and ready to do next step...

Q) Did You add previous years datas already?
>> Ideas ... Questions?

 

[prasanna]

Hi Vletm,

I am collecting the previous year data its huge , I am trying to figure it out how to add 2800 employee per week x 53 weeks and I have added in this forum only 406 employees data.

Once done I will send upload.

Thanks
Prasanna