Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

Excel vba to create table

I have a sub that populates a worksheet from an access query. How do I those rows  into a table using vba?

I found this but it wants a range and the range will change everytime its updated.

When I manually created the table i only needed to click in 1 cell of the data and excel knew the range automatically, so I need to do that action automatically.

 Sub CreateTable()
    Sheets("Pivot SMMT Raw").ListObjects.Add(xlSrcRange, Range("$A$1:$N$16"), , xlYes).Name = _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub
SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
imnorie - I don't have a lot of luck with UsedRange.  Seems that Excel has a way of messing up the UsedRange in my models/workbooks, so I tend to not use it.

Dave
Avatar of Norie
Norie

Dave

I've found that to but it's normally been only when dealing with data that isn't well organised, disjointed, unstructured etc

I'm assuming that if this data is from Access it will be well organised/structured and that there isn't any data on the worksheet.

Could be wrong of course, the data might have been dropped right in the middle of a load of other stuff.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterBaileyUk

ASKER

I hope you didnt mind that I shared the points as both made a contribution.