Setting a Dynamic Table Range in a VBA

[zb14]

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!29889

[Aussiebear]

Please post your code here not an image.

[zb14]

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

[SamT]

It might be as simple as changing

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

Range(A1).CurrentRegion
Or to

UsedRange

[zb14]

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

Final 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