Setting a Dynamic Table Range in a VBA

I am trying to adjust the below VBA so that after it copies the inputted data into a new sheet it also formats it into a table. Currently, the code sets the table to 30 rows. However, because the data I will input changes from project to project, I would like to set the table range to adjust to the amount of rows in the data after the code has run and each of the sheets have been created. Is there a good way that I can do this? I've had trouble changing the range and usually when I alter it I can't get the table to be created at all. Thank you for your time!Attachment 29889

Please post your code here not an image.

Code:

---

Sub AOverheadSheet()
'Updated by Extendoffice 2017/11/10
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Main").UsedRange.Rows.Count
    J = Worksheets("Overhead").UsedRange.Rows.Count
    If J = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("Overhead").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Main").Range("B1:B" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) < 2000 Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Overhead").Range("A" & J + 1)
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
    Sheets("Overhead").Select
    Range("A1:O30").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$O$30"), , xlYes).Name = _
        "Table2"
    Range("Table2[#All]").Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleMedium6"
    Range("Table2[#All]").Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleMedium6"
    Columns("A:A").ColumnWidth = 5.16
    Columns("B:B").ColumnWidth = 4.26
    Rows("1:1").RowHeight = 26.1
    Rows("1:1").RowHeight = 30.9
    Range("Table2[[#Headers],[Costcode Description]:[Column1]]").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("C:C").ColumnWidth = 32
    Columns("D:D").ColumnWidth = 12
    Columns("E:E").ColumnWidth = 15.32
    Columns("F:F").ColumnWidth = 14.16
    Columns("G:G").ColumnWidth = 15.68
    Columns("G:G").ColumnWidth = 17.37
    Columns("H:H").ColumnWidth = 14.74
    Columns("I:I").ColumnWidth = 11.21
    Columns("J:J").ColumnWidth = 12.37
    Columns("K:K").ColumnWidth = 15.05
    Columns("L:L").ColumnWidth = 12.42
    Columns("M:M").ColumnWidth = 9.89
    Columns("N:N").ColumnWidth = 16.68
End Sub

---

It might be as simple as changing

Code:

---

Range($A$1:$O$30)

---

to

Code:

---


Range(A1).CurrentRegion

---

Or to

Code:

---

UsedRange

---

That was it! Wow I'd been wrestling with that for days. Thank you so much!

Final Code

Code:

---

Sub AOverheadSheet()
'Updated by Extendoffice 2017/11/10
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Main").UsedRange.Rows.Count
    J = Worksheets("Overhead").UsedRange.Rows.Count
    If J = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("Overhead").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Main").Range("B1:B" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) < 2000 Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Overhead").Range("A" & J + 1)
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
    Sheets("Overhead").Select
    Range("A1:O30").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = _
        "Table2"
    Range("Table2[#All]").Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleMedium6"
    Range("Table2[#All]").Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleMedium6"
    Columns("A:A").ColumnWidth = 5.16
    Columns("B:B").ColumnWidth = 4.26
    Rows("1:1").RowHeight = 26.1
    Rows("1:1").RowHeight = 30.9
    Range("Table2[[#Headers],[Costcode Description]:[Column1]]").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("C:C").ColumnWidth = 32
    Columns("D:D").ColumnWidth = 12
    Columns("E:E").ColumnWidth = 15.32
    Columns("F:F").ColumnWidth = 14.16
    Columns("G:G").ColumnWidth = 15.68
    Columns("G:G").ColumnWidth = 17.37
    Columns("H:H").ColumnWidth = 14.74
    Columns("I:I").ColumnWidth = 11.21
    Columns("J:J").ColumnWidth = 12.37
    Columns("K:K").ColumnWidth = 15.05
    Columns("L:L").ColumnWidth = 12.42
    Columns("M:M").ColumnWidth = 9.89
    Columns("N:N").ColumnWidth = 16.68
End Sub

---