tlc53
Active Member
- Joined
- Jul 26, 2018
- Messages
- 399
Hi,
I have the below code which is returning an error, I believe near the bottom. It says I need to "End With" but when I put "End With" before "End Sub" it says it wasn't expecting "End With"
I'd really like this to run without any problems. Can someone please show me where I am going wrong?
Thank you!
I have the below code which is returning an error, I believe near the bottom. It says I need to "End With" but when I put "End With" before "End Sub" it says it wasn't expecting "End With"
I'd really like this to run without any problems. Can someone please show me where I am going wrong?
Thank you!
Code:
Sub CleanUp()
Columns("L:Q").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Ticket"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Customer Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Room"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Category"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Pieces"
Range("G1").Select
ActiveCell.FormulaR1C1 = "BD Amount"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Discount %"
Range("I1").Select
ActiveCell.FormulaR1C1 = "AD Amount"
Range("J1").Select
ActiveCell.FormulaR1C1 = "BK Batch"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("A1:K1").Select
Range("K1").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Range("A1").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
"A2:A100000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:K100000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Dim LastNumberRow As Long, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastNumberRow = Evaluate(Replace("MAX(IF(ISNUMBER(A1:A#),ROW(A1:A#)))", "#", LastRow))
Range(LastNumberRow + 1 & ":" & LastRow).Clear
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
"K2:K100000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
"B2:B100000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:K100000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("A:A").Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
Selection.NumberFormat = "m/d/yyyy"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G:G").Select
Selection.NumberFormat = "[$$-en-NZ]#,##0.00"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("H:H").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
Selection.NumberFormat = "[$$-en-NZ]#,##0.00"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("J:J").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("K:K").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:J").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
Columns("A:K").ColumnWidth = 12
Range("A1").Select
End Sub