+ Reply to Thread
Results 1 to 10 of 10

Require VBA working to construct the summary sheet with the help of date calculation

  1. #1
    Forum Contributor PRADEEPB270's Avatar
    Join Date
    02-19-2010
    Location
    INDIA
    MS-Off Ver
    MSoffice-2016
    Posts
    332

    Require VBA working to construct the summary sheet with the help of date calculation

    I have a file with so complicated and want to easy working with the helps of VBA codes.

    Point-1-Construction the summary Report:-
    -Automatic insert unique 'vendor' ( Col.C),Vendor name ( Col.D) and Location (Col.E) from sheet 'MBLB'.
    -Put up the grand total of each vendor in Col.E to J from sheet 'MBLB' .Please refer working in B6,highlight in yellow colour.

    But to prepare the perfect and final working of 'Point-1',we should consider the following factors :-

    1-Col.M in 'MBLB' sheet.It will prepare with the help of "working sheet".
    2-Col.N in 'MBLB' sheet.It is the date difference ( N3-M5).Please Refer the category from summary' sheet. Cell no.E4 to J4.
    3-If #N/A comes the automatic put up >5 Year in col.N.Refer cell no.N5.


    Can the above working be possible through VBA codes only? If yes,please help for the same to construct VBA codes.

    For more clarification,please refer the attach file.

    Regards
    Attached Files Attached Files
    Regards


    Pradeep Kumar Gupta
    Gurgaon ( INDIA )

  2. #2
    Forum Contributor PRADEEPB270's Avatar
    Join Date
    02-19-2010
    Location
    INDIA
    MS-Off Ver
    MSoffice-2016
    Posts
    332

    Re: Require VBA working to construct the summary sheet with the help of date calculation

    Anybody please help me.
    Is there any solution with the help of VBA codes of my query?

    Regards
    Last edited by PRADEEPB270; 10-23-2013 at 11:05 AM.

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Require VBA working to construct the summary sheet with the help of date calculation

    I think your data is not well organized.

    1) When you are looking for a vba code to automate your work, first you must know how to do it manually. And once you know that, you can extract the information on summary sheet based on some logic.When you try to pull data from multiple sheet on summary sheet, you must have a common column on which two sheets can be joined together just like joining two tables based on a common column. This is how I look at it.

    2) But in case, do you want to extract all information on summary sheet from the MBLB sheet itself and you have formulas to populate various data on MBLB sheet? Because I see only data entry on MBLB sheet but no formulas.

    If you are interested in point 2, it should not be a problem. But if you want MBLB sheet to populate with values based on formulas, you must arranged your data as per the discussion in point 1.

    Hope it helps.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Contributor PRADEEPB270's Avatar
    Join Date
    02-19-2010
    Location
    INDIA
    MS-Off Ver
    MSoffice-2016
    Posts
    332

    Re: Require VBA working to construct the summary sheet with the help of date calculation

    OK.

    Please refer the revised attach file,in this,there are only 2 sheets in my attach file,one is 'summary' and other is 'MBLB'. Can you populate the MBLB sheet on summary sheet through VBA codes?
    Attached Files Attached Files

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Require VBA working to construct the summary sheet with the help of date calculation

    Where does the value in column N of sheet MBLB come from? If I am not wrong, you are comparing dates in column M with another date, what is that date?

  6. #6
    Forum Contributor PRADEEPB270's Avatar
    Join Date
    02-19-2010
    Location
    INDIA
    MS-Off Ver
    MSoffice-2016
    Posts
    332

    Re: Require VBA working to construct the summary sheet with the help of date calculation

    22/10/2013 ( 22nd october,2013 ).

  7. #7
    Forum Contributor PRADEEPB270's Avatar
    Join Date
    02-19-2010
    Location
    INDIA
    MS-Off Ver
    MSoffice-2016
    Posts
    332

    Re: Require VBA working to construct the summary sheet with the help of date calculation

    sktneer,now can you populate the 'MBLB' datas on summary sheet?
    However,I have tried my best to understand all the things in my revised file.If any,clarification need,please let me know.

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Require VBA working to construct the summary sheet with the help of date calculation

    Please find the attached sheet and run the macro TransferMBLB on module 2 to see whether you get the desired result.

    I hope it helps.
    Attached Files Attached Files

  9. #9
    Forum Contributor PRADEEPB270's Avatar
    Join Date
    02-19-2010
    Location
    INDIA
    MS-Off Ver
    MSoffice-2016
    Posts
    332

    Re: Require VBA working to construct the summary sheet with the help of date calculation

    Thanks.Codes are working as desired.

  10. #10
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Require VBA working to construct the summary sheet with the help of date calculation

    Good. Thanks for the feedback.

+ 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. [SOLVED] Require VBA codes to prepare the summary report from detail data sheet
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2013, 11:38 PM
  2. Working on Date /time calculation
    By birims in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2012, 03:20 AM
  3. Due date formula - 13-working day SLA calculation
    By einrobinator in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-06-2012, 10:01 AM
  4. [SOLVED] Construct a summary sheet by summing up from one or more than one sheet.
    By PRADEEPB270 in forum Excel General
    Replies: 2
    Last Post: 11-04-2011, 02:26 AM
  5. Two Sheet Calculation/Summary
    By macox700 in forum Excel General
    Replies: 3
    Last Post: 10-10-2010, 09:48 PM

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