Changing Excel Data into a Table

[Saperblue56]

Hi all,
Is it Possable, using VBA to Change normal Data into a Table?

ActiveSheet.ListObjects.Add(xlSrcRange, ("$A$1:$O$") & LastRow, , xlYes).Name = "Table1"
     ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"

So far I Have the above code, but become an timeout Error 5, it does say in German what is wrong, but this would be the wrong Forum for that, cann any one put me on the right road?

Ray

 

[RoryA]

The second argument should be a range:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:O" & LastRow), , xlYes).Name = "Table1"

 

[Saperblue56]

Well firstly Thanks for taking your Time for me RoryA

I tried your Idea :-

Dim Dws As Worksheet
Dim rngLastRow As Long
     Set Dws = ThisWorkbook.Sheets("Basis Daten Inclusive Kommentar")
         
    With Dws
        rngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:O" & LastRow), , xlYes).Name = "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"

but now I get a runtime error 9
Index out of range is a very rough Translation

any Ideas
Ray

 

[RoryA]

OK, well your variable name is not what you posted so it's actually:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:O" & rngLastRow), , xlYes).Name = "Table1"

but the error you are getting suggests that the sheet name is incorrect. Though why you would use a rng prefix for a Long variable I'm not sure.

 

[Saperblue56]

Hi Rory,
How do You Know All This? sorry I dident get back earlyer, but I was on The Road through Europa.
You were right! the Name of the Sheet was the Problem, I wasent Aware that there are Restrictions ! as I shortend it, it works Perfect

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:O" & DataLastRow), , xlYes).Name = "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
    
    Range("A1").Select

I wish you all a very nice weekend

Ray