new in macro

Sem2Zee

New Member
Joined
Oct 8, 2019
Messages
2
Hi all,
i am just started to use macro recently , and i face issue after creating the macro it worked fine for few days then it bring Debug error message although i didn't make any change in it, any advice?


 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,688
Office Version
365, 2010
Platform
Windows, Mobile
Not enough info I am afraid, please post your code and what the error message states.
 

Sem2Zee

New Member
Joined
Oct 8, 2019
Messages
2
Thanks a lot Mark, so wired to day it worked normal. :)

any way the Code below may be your find that is happening


Code:
Sub New_stv()
'
' New_stv Macro
'


'
    Sheets("tsv").Select
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$18488").AutoFilter Field:=1, Criteria1:=Array( _
        "ActivityTime", "CreatedTIme", "Operator time clock 10/1/2019 to 10/2/2019"), _
        Operator:=xlFilterValues
    Rows("1:1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range("$A$1:$A$18488").AutoFilter Field:=1, Criteria1:="="
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A15079").Select
    ActiveSheet.Range("$A$1:$A$15078").AutoFilter Field:=1, Criteria1:=Array( _
        "ActivityTime", "CreatedTIme", "Operator time clock 10/1/2019 to 10/2/2019"), _
        Operator:=xlFilterValues
    Rows("1:1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$E$13712").AutoFilter Field:=1, Criteria1:= _
        "=Fixed Broadband - ADSL / ÅäÊÑäÊ ËÇÈÊ", Operator:=xlOr, Criteria2:= _
        "=Fixed Voice Service  /  ÎÏãÇÊ ÇáÎØ ÃÑÖí"
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A13104").Select
    ActiveSheet.Range("$A$1:$E$13102").AutoFilter Field:=1, Criteria1:= _
        "Mobile /  ãæÈÇíá"
    Rows("213:213").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$E$13030").AutoFilter Field:=1, Criteria1:= _
        "TotalChats"
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$E$12348").AutoFilter Field:=1, Criteria1:= _
        "Assigned Departments"
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$E$11666").AutoFilter Field:=1
    Columns("C:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:B").Select
    Columns("A:B").EntireColumn.AutoFit
    Range("C1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]"
    Range("C2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[-2]),R[-1]C,RC[-2])"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C11666")
    Range("C2:C11666").Select
     Range("D1").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RIGHT(RC[-1],6))"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D2"), Type:=xlFillDefault
    Range("D1:D2").Select
    Range("D1:D2").Select
    Selection.Copy
    Range("D1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D2").Select
    Application.CutCopyMode = False
    Range("$D$2").FlashFill
    Range("C2").Select
    
'
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Status"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Login ID"
    Range("A2").Select
    
'
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-3]),RC[-4],IF(ISNUMBER(RC[-3]),""delete"",RC[-3]))"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E11667")
    Range("E2:E11667").Select
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$E$11667").AutoFilter Field:=5, Criteria1:="delete"
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A9863").Select
    ActiveSheet.Range("$A$1:$E$9861").AutoFilter Field:=5
    Range("C3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[-2]),R[-1]C,RC[-2])"
    Range("C3").Select
    Selection.AutoFill Destination:=Range("C3:C9861")
    Range("C3:C9861").Select
    
'
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.End(xlUp).Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E5").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(11, 1)), TrailingMinusNumbers:=True
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Range("A3").Select
    Cells.Replace What:="me:", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
        
'
    Cells.Replace What:="Operator na", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-4]>R1C5,RC[-3]=""Logged on""),RC[-4],""Off"")"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E10764")
    Range("E2:E10764").Select
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "6:45:00 AM"
    Range("E3").Select
    Range("E1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range("$A$2:$D$10764").AutoFilter Field:=1, Criteria1:="="
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 1
    Rows("103:103").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 352
    ActiveWindow.ScrollRow = 586
    ActiveWindow.ScrollRow = 674
    ActiveWindow.ScrollRow = 645
    ActiveWindow.ScrollRow = 498
    ActiveWindow.ScrollRow = 176
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 1
    Rows("103:103").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$2:$D$5792").AutoFilter Field:=1
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Columns("A:A").Select
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("D5775").Select
    Selection.End(xlUp).Select
    Range("E1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("B6").Select
    
    Range("A2").Select
    ActiveSheet.Range("$A$1:$E$5792").AutoFilter Field:=1, Criteria1:="="
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$E$5440").AutoFilter Field:=1
    Range("D9").Select
    ActiveSheet.Range("$A$1:$E$5440").AutoFilter Field:=4, Criteria1:="Off"
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$E$2342").AutoFilter Field:=4
    Range("D1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range(Selection, Selection.End(xlDown)).Select
    Columns("D:D").EntireColumn.AutoFit
    Range("A1:D1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("D4").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "tsv!R1C1:R2342C4", Version:=6).CreatePivotTable TableDestination:= _
        "tsv!R1C10", TableName:="PivotTable2", DefaultVersion:=6
    Sheets("tsv").Select
    Cells(1, 10).Select
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("6:45:00 AM"), "Count of 6:45:00 AM", xlCount
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of 6:45:00 AM")
        .Caption = "Min of 6:45:00 AM"
        .Function = xlMin
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Login ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    Columns("K:K").Select
    Selection.NumberFormat = "[$-en-US]h:mm AM/PM;@"
    Range("J9").Select
    Columns("J:J").ColumnWidth = 12.86
    Range("H3").Select
     Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("F2327").Select
    Selection.End(xlUp).Select
    Range("D1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1:H1").Select
    Selection.AutoFilter
    Range("D1").Select
    Selection.Cut
    Range("E1").Select
    Range("D1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Time"
    Range("J4").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Time"), "Count of Time", xlCount
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Time")
        .Caption = "Min of Time"
        .Function = xlMin
    End With
    
End Sub
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,099,505
Messages
5,469,017
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top