+ Reply to Thread
Results 1 to 25 of 25

VBA to Create weekly & Monthly wise summary with change shown in another sheet.

  1. #1
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    My Dear Experts,

    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 at main 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 at main summary)

    3. Whatever changes happens in basedetails sheet at any cells file it should go copy and paste the entire row and highlight the changes in RED color in particular week on weekly basis ( this ill go till December. if new employee added it should reflect in YELLOW color ( Created Changes sheet Result ).

    4. I have attached sample employee number.. 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.

    Let me know if any more information required

    I know this is not impossible to you guys in this forum.


    With regards,
    KumarPK
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Kumar,

    So far i dont see any problems with Request 1 or 2

    For Request number 3 i am not sure if i understand. as far as i get it you want it so that if anything changes in the Basedetails sheet a copy of that whole row is put to "Changes Sheet Result" and flagged yellow or Red?

    Also what i dont understand is "WEEK-4- JAN-16" what does that Refer too. The Date the Change happend or the Date in the Column M of Basedetails?

    One Mor Question.
    Ist the Design of the Main Summary Sheet important or could i change the Style (would transpose it so that the Category is in the Columns and the Weeks are in rows for Example)
    Last edited by LordLoki; 01-29-2016 at 05:24 AM.

  3. #3
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Lordloki,

    Request number 3 : Answer is YES , If any cell changes in the basedetails it should reflect in changes sheet results with week number which week its got change.

    WEEK -4 Jan 16 : This is for example if we are in February week3 I need to show last 4 weeks data. that is the reason I have mentioned Week4 Jan 16.

    This is the week number and month I typed wrongly instead of week 4 I have typed Week 4 JAN'16 sorry.

    Sure you can change the style.

    Let me know If any queries...

    Thanks
    KUmarpk
    Last edited by KumarPK; 01-29-2016 at 05:45 AM. Reason: message missing

  4. #4
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Cool will give you a shout if i have something for you.

    One More Question popped up when i looked at the Data
    Its not Clear for me what the Summary of the Month actually is. Do you take the numbers from Week 4 in January or do you pull an average ofver all 4 Weeks?
    Could not tell cause your sample Data is the same every Week :D

    Greets
    Loki

  5. #5
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Yes Sir,

    Every Last week data will be considered for each month calculation.

    Sample data shows everything is same but this numbers is changing by week on week.

    I am eagerly waiting for your shout....

    Thanks
    Kumarpk

  6. #6
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Kumar,

    Find Attached the First Version let me know if that works for you. It does not track the Changes Yet but it does the Summarys
    You just press the Button and it will count everything and write it to the next Free line in the Weekly Summary Sheet.
    On The Monthly and Quarterly Sheet i did not use VBA its just using a Formula to Sum the Last Week of Every Month. You can hide the first row its just there to give you the possibility to change the Last Week of a month to something else then 4.

    On The Weekly Sheet you can also Change the Month Name or Weeknumbers to your needs just change them the same way in the Monthly Sheet so that the Formula still works.

    Its a Simple Approach but it keeps things flexible in my opinion


    I will work on the Changes Tracking Later.
    One Question for that again. Are you the only person changing things in that list or are there other people making changes too?

    Greets
    Loki
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Lord,

    Looks ok, but I need one clarification if I press button why is taking numbers for all the future weeks and months...? can u pls show me some examples if you change to some future weeks and months its reflecting... Pls

    Please add total number of employees in Weekly summary "O" coloumn.

    Formulas is good for summary sheets its looks very nice and easy.

    Thanks
    Kumarpk
    Last edited by KumarPK; 01-29-2016 at 08:21 AM. Reason: Missing information

  8. #8
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    That is because i was assuming that you do a Snapshot of the Data every Week cause i could not find any indicator for the Week Or Month or Date in the Data.
    So i assume on lets Say Monday of January Week 4 you Fill out the Fields based on what is in the Basedetails Sheet. and note that as January Week 3
    So the Button does the Same Thing. It Counts the Numbers in basedetails and Writes them to Weekly summary and Writes it to the Next Free Line which should technically be the Next Week that needs to be filled.

    So if you press the Button now then Change some Guys Departments or Delete them the next time you click the button you will get different Numbers.

  9. #9
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Oh ok I got it...

    I have not put any date because I am doing manually this report and updated week 1 in column a and for week 2 I m copy pasting same as week 1 and changing the date.

    If you want to insert any date column its fine, I don't want to copy and pasting the 2800 employee details each week.

    This dashboard is looking fine and going obsolutely fine.

    One confusion still breaking my head......Still I want to copy and paste the Employee details after Week completed...?? after completion of this dashboard.
    Or you are adding any date field or week field in basedetails which pull automatically...

    Thanks
    Kumarpk
    Last edited by KumarPK; 01-29-2016 at 08:42 AM. Reason: Correction

  10. #10
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hm its confusing for me cause i do not know how Exactly the whole Process Works.

    So i think you get/pull an excel Sheet Every Week that has all the Employee Data like in the "Basedetails" Sheet?
    With my Sheet you would now copy that Data to the basedetails Sheet and press the Button?
    And if Possible you want an Option that makes it so you dont have to Copy that information over manually?

    Is that how it works?

  11. #11
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Yes correct..

    But now, I don't want to add each week employee data to each sheet.

    I need only single sheet "Basedetails", from this sheet I want to pull weekly wise data.

    Hope there is no confusion.

    I know this is very difficult , you want to put date and week column separately in the basedetails its ok.

    I am scaring what is the final result on this, but I hope and have a confident on you and your team definitely will succeed.

    Thanks
    Kumarpk

  12. #12
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    ah the coding part is not so challenging :D

    The challenge is to understand your needs and the Setup you have.
    Do you get the Data that is now in "Basedetails" in a seperate Sheet every week?
    If so could you send add an example of that sheet same like you did with basedetails?

    Cause in the Beginning i was thinking the Data in Base Details is Static and you make changes Directly to that Data.
    But as of now i think you get an Updated List every Week and compare the old list to the new List to track Changes etc

    Greets
    Loki

  13. #13
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Lord,

    Please find attached the updated sheet as per above.

    Jan Wk1, wk2 and wk3 added ; Base details is current week data that is Jan wk4. Hopes this helps.

    Let me know if any more information required.

    Regards,
    Kumarpk
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Lord,

    Hope you are doing good... Any Luck on Thread...??

    Regards
    Kumarpk

  15. #15
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Lord, Any luck...?

    Regards,
    KumarPK

  16. #16
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Lord, Any Luck ...?

    I m waiting for your reply...

    Thanks
    KumarpK

  17. #17
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi lord,

    Any luck, I am waiting for your reply.....

    Thanks
    KumarPK

  18. #18
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    VBA to Create weekly & Monthly wise summary with change shown in another sheet-Incomplete

    Dear Experts,

    This thread has stuck in middle. Kindly do the needful.

    Regards,
    Kumarpk
    With Regards,
    KumarPK

  19. #19
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Unhappy Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Dear Experts,

    This thread has stuck in middle. Kindly do the needful.

    Regards,
    Kumarpk

  20. #20
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Question Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Common on experts you guys are genious in the forum...

    I don't understand why this thread stuck in middle. Please help me "Anybody".

    Regards,

    Kumarpk

  21. #21
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Dear Team,

    Still I have not received any response.......

    Kindly do the needful.

    Regards
    Kumarpk

  22. #22
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Kumar,

    Sorry was not available at the Weekend. Will look at your file Later pretty busy at the moment.

    Greets
    Loki

  23. #23
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Loki,

    How are you...??

    Take your own time...Have a nice day.

    Regards
    Kumarpk

  24. #24
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Loki Good morning,

    Any luck, I hope you will close this thread with happy ending...

    Regards
    Kumarpk

  25. #25
    Registered User
    Join Date
    04-28-2015
    Location
    bangalore
    MS-Off Ver
    2013
    Posts
    56

    Re: VBA to Create weekly & Monthly wise summary with change shown in another sheet.

    Hi Loki,

    Kindly do the needful.

    Regards
    Kumarpk

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-19-2015, 09:42 AM
  2. [SOLVED] monthly summary from multiple weekly time sheets
    By dacct in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-12-2014, 10:36 PM
  3. Task time tracking with daily and weekly and monthly summary
    By issam.ha in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-04-2014, 12:02 PM
  4. [SOLVED] Delivery Report Summary...Monthly total sheet on 4 individual weekly sheets
    By hclark579 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2014, 06:19 PM
  5. Monthly summary of weekly output based on customized calendar
    By thisisgerald in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-18-2012, 08:01 AM
  6. [SOLVED] Monthly Summary Based on Weekly Dates
    By thisisgerald in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-14-2012, 04:49 AM
  7. Excel 2007 : Help with monthly weekly summary
    By kakashi07 in forum Excel General
    Replies: 2
    Last Post: 03-14-2012, 10:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1