none
Object Defined Error in VBA code to create Table RRS feed

  • Question

  • Hi all,

        Range("A4").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = “Table2”

    I'm new to VBA, I'm getting an object defined error 1004 on the bolded line above.

    Anyone know what it could be?

    Thursday, May 24, 2012 3:34 PM

All replies

  • Which version of Excel?

    Code that worked for 97-2003 may not work for 2007 or 2010.

    In Excel 2007, ListObjects is a Read-only property. http://msdn.microsoft.com/en-us/library/bb211471%28v=office.12%29.aspx

    Thursday, May 24, 2012 8:13 PM
  • ListObjects as a collection is read-only, but individual ListObjects are read-write. I recorded a macro in Excel 2010 of me formatting a range as a Table. The recorded code used ListObjects so they are obviously the thing to use! I then tweaked the code to make it more useful:

    Sub Macro1()
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$4").CurrentRegion, , xlYes).Name = "MyTable"
        ActiveSheet.ListObjects("MyTable").TableStyle = "TableStyleMedium4"    'TableStyle 2007 onwards only.
    End Sub
    

    CurrentRegion does the same as Ctrl+* which selects all cells until a blank row or column is reached.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Thursday, May 24, 2012 9:17 PM
  • The posted code works for me in xl2010 (except I had to replace the type of double quotes around Table2). I suggest that you place a stop in the code after the selection and check what range has been selected as per the example below because I think that might be the cause of the error.

    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Stop
    ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Table2"


    Regards, OssieMac

    Thursday, May 24, 2012 9:36 PM