VBA - Convert range into table

[TopLearner]

Good afternoon,

I hope you are all well.

I have a spreadsheet with a tab called "summary" and a range D18:G18 with data on it.
I am trying to convert it into a table by writing a code but it does not work. I have tried two different approaches but they do not work.

Sub ConvertirRangoenTabla()


Dim Rango As Range
Dim Hoja As Worksheet


Set Rango = Worksheets("Summary").UsedRange
Set Hoja = ActiveSheet

Hoja.ListObjects.Add(SourceType:=XlRangoRange, Source:=Rango, xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "Summary"

End Sub

This is giving me this error: "Run-time error '5: Invalid procedure call or argument


Then, I try another one

Sub ConvertirRangoenTablaII()


Dim Rango As Range
Dim Hoja As Worksheet


Set Rango = Range("D3").CurrentRegion
Set Hoja = ActiveSheet


Hoja.ListObjects.Add(SourceType:=XlRangoRange, Source:=Rango, xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "Summary"


End Sub

And, I get the same error
"Run-time error '5: Invalid procedure call or argument


Can you please help?

Thanks
Sanchez

 

[MARK858]

Try changing

Hoja.ListObjects.Add(SourceType:=XlRangoRange, Source:=Rango, xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "Summary"

to

Hoja.ListObjects.Add(SourceType:=xlSrcRange, Source:=Rango, xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "Summary"

 

[TopLearner]

Try changing

Hoja.ListObjects.Add(SourceType:=XlRangoRange, Source:=Rango, xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "Summary"

to

Hoja.ListObjects.Add(SourceType:=xlSrcRange, Source:=Rango, xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "Summary"

Thanks Mark,

My question here is, why xlSrcRange if my variable is called Rango. Is Src something that must be always used? Any reasons for that?

Does Src stand for "Source" and that's why we add Source:=Rango to the code?

Regards
Sanchez

 

[MARK858]

It is not a variable, it is a source type. It tells Excel where it is getting the data from.
The source types are


xlSrcExternal
xlSrcModel
xlSrcQuery
xlSrcRange
xlSrcXml

 

[TopLearner]

It is not a variable, it is a source type. It tells Excel where it is getting the data from.
The source types are


xlSrcExternal
xlSrcModel
xlSrcQuery
xlSrcRange
xlSrcXml

Many thaNKS