Sana po may makatulong.. Need ko lang ng expert na mag eedit ng macro ko sa excel.. araw-araw kasi nagbabago yung number of rows pero yung column constant. E gusto ko pagpaste ko ng values sa proper columns nila, irurun ko na lang yung macro..
Eto po yung code:
Sub Macro8()
'
' Macro8 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=NOW()-RC[-1]"
Range("C2").Select
Selection.NumberFormat = "0.00"
Selection.AutoFill Destination:=Range("C2:C157"), Type:=xlFillDefault
Range("C2:C157").Select
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollRow = 1
Range("P2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("P2").Select
Selection.NumberFormat = "m/d/yyyy"
Selection.AutoFill Destination:=Range("P2: P157"), Type:=xlFillDefault
Range("P2: P157").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-39
ActiveWindow.ScrollRow = 1
Range("A1157").Select
Range("C2").Activate
Selection.AutoFilter
ActiveWorkbook.Worksheets("SLA - UNTOUCHED TICKET").AutoFilter.Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("SLA - UNTOUCHED TICKET").AutoFilter.Sort.SortFields. _
Add2 Key:=Range("C1:C157"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("SLA - UNTOUCHED TICKET").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("C2:C157").Select
Windows("BOH CAPTURE - ERLYN AND YUJ.xlsm").Activate
ActiveWindow.ScrollRow = 1
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=3"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=3"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.ScrollRow = 1
End Sub
Thanks po sa tutulong
Eto po yung code:
Sub Macro8()
'
' Macro8 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=NOW()-RC[-1]"
Range("C2").Select
Selection.NumberFormat = "0.00"
Selection.AutoFill Destination:=Range("C2:C157"), Type:=xlFillDefault
Range("C2:C157").Select
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollRow = 1
Range("P2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("P2").Select
Selection.NumberFormat = "m/d/yyyy"
Selection.AutoFill Destination:=Range("P2: P157"), Type:=xlFillDefault
Range("P2: P157").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-39
ActiveWindow.ScrollRow = 1
Range("A1157").Select
Range("C2").Activate
Selection.AutoFilter
ActiveWorkbook.Worksheets("SLA - UNTOUCHED TICKET").AutoFilter.Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("SLA - UNTOUCHED TICKET").AutoFilter.Sort.SortFields. _
Add2 Key:=Range("C1:C157"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("SLA - UNTOUCHED TICKET").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("C2:C157").Select
Windows("BOH CAPTURE - ERLYN AND YUJ.xlsm").Activate
ActiveWindow.ScrollRow = 1
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=3"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=3"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.ScrollRow = 1
End Sub
Thanks po sa tutulong