+ Reply to Thread
Results 1 to 25 of 25

Adding Order data to MASTER

  1. #1
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Adding Order data to MASTER

    Dear All,

    I have a table MASTER to enter all my vouchers. (This is in sheet "MASTER")

    I will take orders separately in tables one table per order. (These tables will be in sheet "ORDERS")

    Once the order is completed, I will transfer/add it's data to [MASTER] table and want VBA to do the same.

    When we started macro, A form with below fields to be appeared.

    1. SOURCE 2. REF NO 3. DATE 4. PARTY 5. DORC

    (Here SOURCE is the Order Table Name)

    I will fill all the fields and submit.

    Now the Macro Functions are,

    1. Count the data rows in Source/Order Table (assume that it is N)

    2. A value " SALES " be filled N times to the end of MASTER[TYPE] column

    3. Given DATE value be filled N times to the end of MASTER[DATE] column

    4. Given REF NO value be filled N times to the end of MASTER[REF NO] column

    5. Given PARTY value be filled N times to the end of MASTER[PARTY] column

    6. SOURCE[P CODE] total column to be pasted @ MASTER[P CODE] end

    7 SOURCE[QTY] total column to be pasted @ MASTER[QTY] end

    IF DORC value in form is DEBIT then

    8. SOURCE[TOTAL] total column to be pasted @ MASTER[DEBIT] end ELSE
    8. SOURCE[TOTAL] total column to be pasted @ MASTER[DEBIT] end & MASTER[CREDIT] end too

    Please find the attachment.

    Thanks in advance.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Hi kprabhupaul

    I've looked at your Workbook at least 6-8 times and am trying to sort through what you're trying to do...to understand your process.
    On Worksheet "ORDERS" you have 5 Tables
    1. SBTO101
    2. SBTO102
    3. SBTO103
    4. SBTO104
    5. Order

    What is the significance of Table "Order"? How is it used? Why is it there?


    Will there ever be more than these 5 Tables?

    As I understand, you will fill out these 5 Tables with the required information. Having done so, you wish to have the Information transferred to Sheet "MASTER".

    You wish to have a UserForm to fill out in which you'll supply:
    The SOURCE (you'll input which Table into the UserForm)
    The REF NO (you'll input the Reference Number into the UserForm)
    The DATE (you'll input the Date into the UserForm)
    The PARTY (you will have input this value into the Order Book Table above the Table Header, Column B)
    The DORC (you'll input Debit or Credit into the UserForm)

    Is my understanding correct? Whatever other information you can share regarding your process will be helpful.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    firstly thanks for the reply brother ..

    Ok, for your better understanding I will simply the question and will reply once it was done.

    Thanks for your patience ..

  4. #4
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    Dear Jaslake,

    You understood almost very well.

    The More appropriate title for Macro's work is " Filling one Table column with the values of another table column "

    At first I will take and enter order details in [ORDER] Table (in ORDERS sheet) and same table will be placed left side after taking is complete.

    And at evening I have to add all of the order data to the columns of [MASTER] Table (in sheet "Master").

    When macro runs a popup with userform should be appeared to tell macro what is 1. SOURCE TABLE, 2. DATE 3. REF NO & 4. PARTY

    Then fill columns of [MASTER] Table in sheet "Master"

    TYPE as fixed value "SALES"

    DATE to be filled with the given value in userform

    REF NO to be filled with the given value in userform

    P CODE to be filled from the said source table [P CODE] column

    QTY to be filled from the said source table [QTY] column

    DEBIT to be filled from the said source table [DEBIT] column (only values)

    CREDIT to be filled from the said source table [CREDIT] column. (only values)

    PARTY to be filled with the given value in userform

    Please find the new attachment.
    .
    That's all. Thank You Dear
    Attached Files Attached Files
    Last edited by kprabhupaul; 02-28-2017 at 02:07 AM.

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Hi kprabhupaul

    Please check these Formulas...some don't look right to me...

    Formulas.jpg

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    How many Tables will you end up with on the Left Side...will these Tables already exists or will the Code need to create them?
    At first I will take and enter order details in [ORDER] Table (in ORDERS sheet) and same table will be placed left side after taking is complete

  7. #7
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    Sorry, I forgotten to give you the attachment with some orders.
    I'm in mobile now. I will give you within 2 hours ..

    I think this would the more appropriate title for Marco's work.

    Filling the column of one table with the values of column of the another table.

    There is no minimum and maximum limit for the order tables.

    I will create them from morning and all those will be deleted at evening after adding to master.


    Thank You
    Last edited by kprabhupaul; 02-28-2017 at 02:07 AM.

  8. #8
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    Here is the attachment ..
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    userform.jpg

    userform should like this ..

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Hi kprabhupaul

    Play with this Code in the attached. Let me know.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    Awesome Brother Jaslake,

    All is working very fine.

    And I didn't think that's getting PARTY Name is so simple,

    So, I changed my mind.

    Like PARTY Name, I placed Date & REF NO values too at above table head ( loRowNum = .HeaderRowRange.Row - 1 )

    So now we don't need any text boxes in form. Just only Source Table combobox is enough.

    Please find the image & file attachments and do the needful ..

    Thank You so Much .. [ADDED REPUTATIONS]
    Attached Images Attached Images
    Attached Files Attached Files

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Hi kprabhupaul

    I've left the Party TextBox3 in the UserForm as it's the only Clue to the User that they've selected the proper Order. If you don't want it there you can change it's Visible Property to False. Alternately, you can Delete it and also Delete all References to it in the Code, else, let me know and I'll do so.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    No Brother,
    If it is possible without textboxess then I don't want them. Just place only combo box (drop down).

    Because I will include REF NO in the orders tables names, so the name itself is a clue ..

    Pls do the needful n let me know. TanQ
    Last edited by kprabhupaul; 03-01-2017 at 01:23 PM.

  14. #14
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Hi kprabhupaul

    Here you go...

    Quote Originally Posted by kprabhupaul View Post
    No Brother,
    If it is possible without textboxess then I don't want them. Just place only combo box (drop down).

    Because I will include REF NO in the orders tables names, so the name itself is a clue ..
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    I'm in mobile now,

    I will check it from office tomorrow and will tell you..

    Thanks again....

  16. #16
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    Can you give the modified code here,...?
    Because I can't open it in mobile..
    I want to see how things changed..

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Sure...here you go...please note the TextBox Control and it's Label have been removed from the UserForm...
    Can you give the modified code here,...?
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    Still I found the textboxess references,
    Have you not deleted them ..?

    Please delete all the textboxess references from the code.

    I want only the drop down
    Thank you brother ..

  19. #19
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Hi kprabhupaul

    Those TextBox References have been Commented Out and the TextBoxes have been removed from the UserForm. You still have the ComboBox Dropdown (and ONLY the ComboBox Dropdown).

  20. #20
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    Then delete all the code lines, which the textboxess references are in.

    AND MAKE THE CODE SIMPLE..

    THANKS

  21. #21
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Can't make the Code "Simple"...it's a rather complex request.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    Hi Brother,

    Instead of Userform combobox, I just applied data validation with table names to MASTER!N1

    1.JPG

    So it gives the names of tables in "ORDERS" Sheet.

    and just given a button below to it ( MASTER!O1 ).

    Can you assign the userform's submit button function to this button..?

    Then we have no need with Userform (we can delete it)

    onemore thing is I want two msg boxes when,

    1. Before the start of submit button function

    2.JPG

    2. After the End of submit button function

    3.JPG

    Please find the attachments, Thank You

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kprabhupaul; 03-02-2017 at 09:00 AM.

  23. #23
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Hi kprabhupaul

    Try this Code in the attached...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    06-02-2016
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    327

    Re: Adding Order data to MASTER

    Thanks for that ..

    I just made small modifications to the code.

    1. Please add a function to delete order data (table and it's above header row) after it is transferred to target range.

    2. The calculation process is bit slow, if it is possible to speed up, please do it.

    Thanks again. Good Day ahead ..

    Please Login or Register  to view this content.
    Attached Files Attached Files

  25. #25
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Order data to MASTER

    Hi kprabhupaul

    The Code in the attached has been modified for these
    Please add a function to delete order data (table and it's above header row) after it is transferred to target range.

    The calculation process is bit slow, if it is possible to speed up, please do it.
    The previou Code had a redundant Re-Calculation that has been removed. However, recalculating all the Formulas in the Workbook is going to be inherently slow.
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. Master Stock list & Order spreadsheet assistance.
    By jkility in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2016, 09:32 AM
  2. Adding data to Master Lists
    By Fenway94i8 in forum Excel General
    Replies: 1
    Last Post: 03-31-2016, 04:49 AM
  3. Find value on two sheets and copy in correct order on master list
    By danfullwood in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2015, 08:19 PM
  4. Replies: 0
    Last Post: 10-02-2014, 12:54 AM
  5. Master inventory product price changes with new order
    By Mmurra22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2013, 09:13 PM
  6. [SOLVED] Sales order form from a master product list
    By dawondr in forum Excel General
    Replies: 3
    Last Post: 05-04-2013, 08:42 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