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
Microsoft Excel

Avatar of undefined
Last Comment
PeterBaileyUk

8/22/2022 - Mon
SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dlmille

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
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PeterBaileyUk

ASKER
I hope you didnt mind that I shared the points as both made a contribution.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck