Macro hard coding cell reference when creating a table

[Noddy13]

Hello, I am trying to set up a macro that will select a varying amount of data and then format it into a table. I'm selecting the data using the ctrl shift keys to get to the end cells however when I create the table it locks in the cells from the original selection so the table size is static even though the selected area varies. What am I missing?

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$E$10"), , xlNo).Name = _
"Table5"
Range("Table5[#All]").Select
ActiveSheet.ListObjects("Table5").TableStyle = "TableStyleLight2"

All advise appreciated.

Thanks
Jamie

 

[My Aswer Is This]

Assuming you want to select a range and then have that range now be a table.
Try this:

Be sure your Table name is not repeated:

Sub My_New_Table()
'Modified  4/16/2019  2:25:03 AM  EDT
ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlNo).Name = _
"Table5"
Range("Table5[#All]").Select
ActiveSheet.ListObjects("Table5").TableStyle = "TableStyleLight2"
End Sub