+ Reply to Thread
Results 1 to 5 of 5

macro to automatically insert table in excel

  1. #1
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    macro to automatically insert table in excel

    Hello,

    I am trying to write a MACRO that I can use at work.

    My goal is to have a Macro that would automatically insert a table with headers around the data after it's export into Excel.

    The data I export always populates into Excel in the same cells but from different accounts can have much more rows than others -So How do make the code accommodate for that?



    So I experimented with some Data and I recorded the Macro of me inserting the table with headers...

    The main thing I would Love to know is to how to adjust the Macro to accommodate for different rows of info that might appear.

    Also

    Once the Macro is in Excel if I export new Data do I just save it to a workbook in order to enable myself to run it for anything new that I would be exporting in or How do I run it once the macro is in excel (Do I find the module and run it?)?

    Attached is my excel spreadsheet with the Macro.


    Please if there is any confusion, Let me know.

    Thank you In advance.
    Attached Files Attached Files

  2. #2
    Forum Contributor codeslizer's Avatar
    Join Date
    05-28-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    245

    Re: macro to automatically insert table in excel

    I believe introducing a dynamic named range should meet your requirements. To create one, you can use OFFSET function. It takes in five parameters..

    1. REF : From where you wish to set reference and start, usually, the left-topmost cell of the table. Say REF be A1
    2. ROWS : is the number of rows you wish to switch from REF. Say I want 3rd row from REF cell (3 times down) so cell would offset to A4.
    3. COLS : is the number of columns you wish to switch from REF. Say I want 2nd Column from REF cell (2 times right) so cell would now offset to C4.
    4. HEIGHT : This is what you were seeking, the length of the table can be set here. so I use COUNTA to get the count of filled rows in a column. In your file:- A1:A149
    5. WIDTH : This is the second thing which you'd need to set to get the width of your table. In your file:- A:W

    So, here's the function that you need to define in Named Range (RefersTo). Let its name be "RAW_DATA"
    Please Login or Register  to view this content.
    Following is the general code I created that can be used to create a table and convert it back to range
    Please Login or Register  to view this content.
    and here goes its implementations:

    Please Login or Register  to view this content.
    I have re-worked on your file. Probably this is what you are looking for. Let me know if it isn't..
    Attached Files Attached Files
    Last edited by codeslizer; 08-14-2013 at 05:38 AM.
    cOdEsLiZeR - Back after a long break.. Let's sLiZe some more cOdEs!!

  3. #3
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: macro to automatically insert table in excel

    Dude, That is sooo sweet. Like I can't believe it! I must say...I am new to VBA and a little bit overwhelmed with the code. But I am really impressed with what you did! Thank You!!!

  4. #4
    Forum Contributor
    Join Date
    03-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    491

    Re: macro to automatically insert table in excel

    Can any one explain this first row of code?

    ActiveSheet.ListObjects.Add(xlSrcRange, useThisRange, , xlYes).Name = tbName

  5. #5
    Forum Contributor codeslizer's Avatar
    Join Date
    05-28-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 - 2010
    Posts
    245

    Re: macro to automatically insert table in excel

    Yeah sure, that's actually creating the DataTable for you. It has the same meaning as
    Please Login or Register  to view this content.
    simply to create the table. But I made it dynamic by converting the range and name part to parameters. So, next time instead of writing this whole line of code, it can be simply replaced by
    Please Login or Register  to view this content.

+ 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. Macro to insert new row in Excel 2010 table (ListObject) in protected worksheet
    By kinsley in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-10-2012, 02:57 PM
  2. Automatically insert rows and columns as pivot table changes
    By Jayrome in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2012, 03:41 AM
  3. Insert Text in a table cell in MS word from excel macro
    By jrp_167 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2011, 01:02 PM
  4. Macro to insert photo automatically in EXCEL 2007
    By Niconatu in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-14-2010, 06:17 AM
  5. Replies: 1
    Last Post: 02-08-2005, 06:06 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