Results 1 to 5 of 5

Thread: Macro to create detailed payment schedule using summary list of bank facilities

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Posts
    14
    Location

    Post Macro to create detailed payment schedule using summary list of bank facilities

    I have a table of bank facilities with different types and details (table called "Facility_Summary"). I am looking for your help to create a macro, which will create a detailed payment schedule based on the remaining payments of each applicable facility (Remaining PMTs) in a new worksheet using details included in the subject table following below logic or validation:

    Step 1 - Create a new worksheet called "Schedule"
    Step 2 - Within the "Facility_Summary" Table, validate each line item and apply the following logic: If facility type ="Credit Card" or "Overdraft", then skip and move to the next row,
    Otherwise create number of rows based on number of "Remaining PMTs" in the the "Facility_Summary" Table with the following data:

    Sr / Bank / Facility Type / EMI / Date

    Important Note with regards to Date column: Macro will use "Next PMT Date" as point of reference for the first created detailed payment. Second detailed payment for the same facility (if applicable) will be created based on subject facility "Frequency" in the "Facility_Summary" Table (i.e. if frequency is monthly, then the date of second detailed payment shall be based on same reference date + 1 month).

    I have attached along with this post a sample file with summary table and what I want to achieve from the requested macro.
    I have a table of bank facilities with different types and details (table called "Facility_Summary"). I am looking for your help to create a macro, which will create a detailed payment schedule based on the remaining payments of each applicable facility (Remaining PMTs) in a new worksheet using details included in the subject table following below logic or validation:

    Step 1 - Create a new worksheet called "Schedule"
    Step 2 - Within the "Facility_Summary" Table, validate each line item and apply the following logic: If facility type ="Credit Card" or "Overdraft", then skip and move to the next row,
    Otherwise create number of rows based on number of "Remaining PMTs" in the the "Facility_Summary" Table with the following data:

    Sr / Bank / Facility Type / EMI / Date

    Important Note with regards to Date column: Macro will use "Next PMT Date" as point of reference for the first created detailed payment. Second detailed payment for the same facility (if applicable) will be created based on subject facility "Frequency" in the "Facility_Summary" Table (i.e. if frequency is monthly, then the date of second detailed payment shall be based on same reference date + 1 month).

    I have attached along with this post a sample file with summary table and what I want to achieve from the requested macro.
    I have a table of bank facilities with different types and details (table called "Facility_Summary"). I am looking for your help to create a macro, which will create a detailed payment schedule based on the remaining payments of each applicable facility (Remaining PMTs) in a new worksheet using details included in the subject table following below logic or validation:

    Step 1 - Create a new worksheet called "Schedule"
    Step 2 - Within the "Facility_Summary" Table, validate each line item and apply the following logic: If facility type ="Credit Card" or "Overdraft", then skip and move to the next row,
    Otherwise create number of rows based on number of "Remaining PMTs" in the the "Facility_Summary" Table with the following data:

    Sr / Bank / Facility Type / EMI / Date

    Important Notes with regards to Date column:
    - Macro will use "Next PMT Date" as point of reference for the first created detailed payment. Second detailed payment for the same facility (if applicable) will be created based on subject facility "Frequency" in the "Facility_Summary" Table (i.e. if frequency is monthly, then the date of second detailed payment shall be based on same reference date + 1 month).
    - For future dates, I don't require the accurate day. What is important for me is the accurate month and year as this is required for my analysis. Accordingly, I don't mind to make all future payments with day 15 for example.

    Important Note with regards to number of remaining PMTs: I have added this column in my Summary sheet as a formula. It would be great if you can include it in your code in order not to confuse end user when collecting summary details. However, I don't mind keeping it as worksheet formula (As you will see in the attached example sheet)

    I have attached along with this post a sample file with summary table and what I want to achieve from the requested macro. Thanks in advance.
    Attached Files Attached Files

Posting Permissions

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