Results 1 to 5 of 5

Thread: How to create macro to auto populate selected / required data into tables ?

  1. #1

    How to create macro to auto populate selected / required data into tables ?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys,

    I need your help as I'm not familiar on how to create macros in MS Excel.

    I have a set of data relating to events which I extract from one of our databases on a regular basis. The event has a unique identifier which is the Event Id in column A as indicated in the Data Tab attachment. Each event has its own event details pertaining to each field (i.e. columns) as indicated in the Data Tab attachment.

    What I would like for the macro to do is to populate the selected / required data as per the column headings highlighted in green in the Data Tab attachment corresponding to each Event Id onto the relevant fields in the table as indicated in the Table Tab attachment. One table for one event.

    What I would also like for the macro to do is to be able to identify how many Event Ids there are in the Data Tab attachment and create / replicate the number of tables (including the relevant fields and cell / table formatting) required accordingly. For example, if there are 50 Event Ids in the Data Tab attachment then there will be 50 tables created / replicated prior to the selected / required data been automatically populated onto the relevant fields in each table corresponding to each Event Id.

    The tables should be listed in order corresponding to the Event Ids listed in the dataset eventhough the event Ids are not sorted by value from smallest to largest or vice versa. In addition, the dataset and tables need to be on separate tab / spreadsheet within the workbook.

    Thanks in advance for your help.

    Kind regards,

    Charles
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Data Tab.jpg 
Views:	119 
Size:	20.3 KB 
ID:	1734   Click image for larger version. 

Name:	Table Tab.GIF 
Views:	128 
Size:	15.8 KB 
ID:	1735  

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    An image doesn't help us to understand your Excel problem. Attach a dummy workbook
    Hope that helps

    Roy

  3. #3
    As requested, here is the dummy workbook for your reference. Appreciate your help. Thanks.
    Attached Files Attached Files

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    You can achieve that simply with VLOOKUP and possibly a data Validation list
    Attached Files Attached Files
    Hope that helps

    Roy

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Your request was a macro to create the required number of tables and fill them in from the Data sheet.

    Have a look at this, I've added a separate sheet to hold a table (really more a form) to use for copying.

    Hope this is of some use to you.
    Good luck with the project
    NoS
    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
  •