Record Macro to Create Dynamic Table!!

[SAQIBN]

while recording Macro for Dynamic Pivot Table, i uses "Offset" and then " Define a name for Range",

Now i want to Record a macro, to convert range to table . (may be every time with different data size)..
but unable to do so....

 

[My Aswer Is This]

I f your wanting to select a Range on your worksheet and then convert that range to a Excel Table then try this:
Select the range where you want the Table and then run this script:

Sub Add_Table()
'Modified  10/16/2021  8:30:39 PM  EDT
Dim ans As String
On Error GoTo M
ans = InputBox("Enter Name you want new table named")
ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = ans
Exit Sub
M:
MsgBox "You attempted to name your table a table name that exist already" & vbNewLine & "You tried" & ans
End Sub