VBA To Change Range into a table

[jondavis1987]

Below is an error handling code inside of a much larger vba. In it the error inserts a value into A1:C1. Then when it resumes the code it will insert information into those columns in the last row. How can I have the vba turn that into a table with A1:C1 as headers? This is inside of a very large workbook with tables being added somewhat regularly.

ErrHandler:
    If Err.Number = 9 Then
        ' sheet does not exist, so create it
        Worksheets.Add.Name = wsName
        destWB.Sheets(wsName).Range("A1").Value = "Date"
        destWB.Sheets(wsName).Range("B1").Value = "Mold Height"
        destWB.Sheets(wsName).Range("C1").Value = "AC"
         destWB.Sheets(wsName).Range("F2").Value = "Avg Height"
          destWB.Sheets(wsName).Range("F3").Value = "Avg AC"
           destWB.Sheets(wsName).Range("G2").Value = "=Average(B:B)"
            destWB.Sheets(wsName).Range("G3").Value = "=Average(C:C)"
            destWB.Sheets(wsName).Range("A1", "A5000").NumberFormat = "mm/dd/yyyy"
            destWB.Sheets(wsName).Range("B1", "B5000").NumberFormat = "0.0"
            destWB.Sheets(wsName).Range("C1", "C5000").NumberFormat = "0.00"
        ' go back to the line of code that caused the problem
       Resume
       End If
       
End Sub

 

[mole999]

recording in macro i get

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$B$2"), , xlYes).Name = "Table1"
Range("Table1[#All]").Select

so A1:G5000 or similar style of ranges

 

[jondavis1987]

I had to change it a little bit to

ActiveSheet.ListObjects.Add(xlSrcRange, destWB.Sheets(wsName).Range("$A$1:$C$1"), , xlYes).Name = wsName

your code was much simpler than what I was looking at but because of tables frequently being added I needed a way for the table name to be dynamic and wsName refers to cell B6 in a different workbook.