Skip to content

Excel VBA – How to Convert Range into Excel Table Using VBA Macro

    How to Convert Range into Excel Table Using VBA Macro

    To convert a range into an Excel table use Listobjects.Add. Listobjects is a property of the Worksheet object. Add is a method of Listobjects. Add has the following parameters.

    expression .Add(SourceType, Source, LinkSource, HasHeaders,Destination)

    Use xlSrcRange as your SourceType.

    To apply a particular style to your table get the style name by hovering over a style thumbnail in Excel.

    Dim src As Range
Dim ws As Worksheet
Set src = Range("B5").CurrentRegion
Set ws = ActiveSheet
ws.ListObjects.Add( SourceType:=xlSrcRange, Source:=src, _
xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "Sales_Table"