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