How do I create a table from selected columns in a dynamic range

[davidmyers]

Hi, how do I display selected columns from a dynamic range in a table, make changes in the table and insert the changes back into the worksheet?


Thanks for any help
David

 

[davidmyers]

This is the code I came up with

Private Sub CreateTable()
Dim frstRow As Long, lstRow As Long, cnt As Long, i As Long, j As Long
Dim rng As Range
Dim TableName As String
   cnt = 0
    Do While (ActiveCell.Offset(cnt, 0).Value = ActiveCell.Offset(cnt - 1, 0).Value)
       cnt = cnt - 1
    Loop
    frstRow = ActiveCell.Row + cnt
    cnt = 0
    Do While (ActiveCell.Offset(cnt, 0).Value = ActiveCell.Offset(cnt + 1, 0).Value)
       cnt = cnt + 1
    Loop
    lstRow = ActiveCell.Row + cnt
    Set rng = Application.Range("Complaints!A" & frstRow & ":K" & lstRow)
    With rng
        .Sort Key1:=.Range("Complaints!D" & frstRow & ":D" & lstRow), Order1:=xlAscending _
             , Key2:=.Range("Complaints!E" & frstRow & ":E" & lstRow), Order1:=xlAscending _
             , Key3:=.Range("Complaints!F" & frstRow & ":F" & lstRow), Order1:=xlAscending, Header:=xlGuess
    End With
    Set rng = Application.Range("Complaints!A" & frstRow & ":K" & lstRow)
    TableName = "SpeciesTbl"
    ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = TableName
End Sub

I'm getting an error 1004 "a table can't overlap another table"

This is my 1st attempt at tables - was expecting to see a table that I could work on open on top of the ActiveSheet is that correct?

Please could you point me in the direction to do something like this

Thanks
David