new in macro

S

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?


 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Not enough info I am afraid, please post your code and what the error message states.
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Cannot Exit Design Mode Error
Replies
5
Views
201
bisel
B
B
  • Solved
Error on method intersect
Replies
2
Views
66
Beneindias
B
P
  • Question Question
Excel/365 VBA crash issues recently
Replies
0
Views
425
picklefactory
P
S
  • Solved
Pivot Table Macro
Replies
4
Views
383
SCPbrito
S
B
  • Question Question
Custom View Fails with "Can't push objects off the sheet", but there are no objects!
Replies
5
Views
129
BoredTrevor
B

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top