• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

not able to calculate formula to all rows

but where should i add this in the code and should i keep all the version information and bank information as usual in the code??.
 
and version and bank names keep changing daily...it wont look same as the above code does it work if any thing like version no and bank name changes from the list. no may reduce or increase, how to change the code in perticular place..
 
If you only wish to show data that does not start with "FINCRM 10.3." then this:
Code: 
ActiveSheet.Range("$A$1:$AE$4716").AutoFilter Field:=5, Criteria1:=Array( _
        "10.02.09", "10.1.09", "10.1.10", "10.1.9", "10.2", "10.2.05", "10.2.06", "10.2.09", _
        "10.2.10", "10.2.11", "10.2.12.01", "10.2.12.02", "10.2.13", "10.2.13 Lite", _
        "10.2.14", "10.2.15", "10.2.16", "10.2.17", "10.2.18", "10.3.0", "10.3.06", "10.3.4", _
        "10.3.8", "11", "11.1", "11.2", "6.2.06", "7.1", "9.2", "CRM 10.1.06", "CRM 10.1.08", _
        "CRM 10.1.09", "CRM 10.2.02", "CRM 10.2.02.1", "CRM 10.2.04", "CRM 10.2.05", _
        "CRM 10.2.08", "CRM 10.2.09", "CRM 10.2.13", "CRM 6.2.03", "CRM 6.2.06", "CRM 6.3", _
        "CRM 7.1", "CRM 9.2", "FIN 10.1.06.02", "FIN 10.1.09", "Fin 10.2.03", "FIN 10.2.09", _
        "FIN 10.2.11", "FIN 10.2.13", "FIN 10.2.14", "FIN 10.2.15", "FIN 10.2.16", _
        "FIN 10.4.2", "FIN 7.1", "FIN 9.6.07", "FINCRM 10.1.03", "FINCRM 10.1.06", _
        "FINCRM 10.1.08", "FINCRM 10.1.09", "FINCRM 10.1.10", "FINCRM 10.2.02", _
        "FINCRM 10.2.03", "FINCRM 10.2.04", "FINCRM 10.2.05", "FINCRM 10.2.06", _
        "FINCRM 10.2.07", "FINCRM 10.2.08", "FINCRM 10.2.09", "FINCRM 10.2.10", _
        "FINCRM 10.2.12", "FINCRM 10.2.13", "FINCRM 10.4", "FINCRM 9.2", "FINCRM 9.2.01"), _
        Operator:=xlFilterValues
Click to expand...

would become:
Code: 
ActiveSheet.Range("$A$1:$AE$4716").AutoFilter Field:=5, Criteria1:="<>FINCRM 10.3.*", _
        Operator:=xlFilterValues
 
narsing rao said:
and version and bank names keep changing daily...it wont look same as the above code does it work if any thing like version no and bank name changes from the list. no may reduce or increase, how to change the code in perticular place..
Click to expand...
Since you can have more/less data, instead of "ActiveSheet.Range("$A$1:$AE$4716")...", you can use "ActiveSheet.Range("A:AE")..."
 
Hi Pcosta,

check below code..

Code: 
Line 1 = ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=19, Criteria1:=Array( _
        "ALL OTHERS", "AMLA", "APP-ISSUE", "CHANGE REQUEST", "CLARIFICATION", _
        "CLARIFICATION_SOUGHT", "Clarifications-Due to Finacle Knowledge", "Code Review", _
        "Coding Error", "CR", "Critical Patches", "CUSTOMISATION_DESIGN", _
        "CUSTOMISATION_PERFORMANCE", "CUSTOMIZATION", _
        "Customization-Interfaces Issues-Design Issue", "DATA MIGRATION", _
        "Data Migration Error", "Data Related Issues-Data Mapping Issue", _
        "Data Related Issues-Incomplete Data", "Data Related Issues-Wrong Data", _
        "Deployment Error", "Design Error", "DESIGN ISSUES", "DOCUMENTATION", "DUPLICATE", _
        "Enhancement", "EOD/BOD", "Essential Patches", "IMPLEMENTATION", _
        "IMPLEMENTATION OF NEW PRODUCTS", "INFRA_REQMTS", "Infrastructure Error", _
        "INTERFACE", "Invalid Requirement", "L2 Production suport", "Localization", _
        "NEW REQUIREMENT", "NOT AN ISSUE", "OBSERVATION", "OPERATING SYSTEM", "OTHERS", _
        "Parameter Error", "PARAMETERIZATION ISSUES", "PERFORMANCE", "Porting", _
        "PRODUCT BUG", "Product Bug-Bug in the Product", _
        "Product Bug-Product Enhancement", "Product Dependency", "Product Feature", _
        "Production", "Recon", "Regression Testing", "REPORT", "SET UP ISSUES", "SIT_BUG", _
        "SOFTWARE ENHANCEMENT REQUEST", "SOFTWARE ERROR/TROUBLE FIX REQUEST", _
        "Technical Issue-Configuration Issue", "Test", "Third party issue", "TRAINING", _
        "UAT_BUG", "USER_MISTAKE", "WARRANTY", "WAS", "="), Operator:=xlFilterValues
      ("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
        "PRODUCTION"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Production Calls").Select
    ActiveSheet.Paste
    Sheets("Main").Select
    ActiveWindow.ScrollRow = 3314
    ActiveWindow.ScrollRow = 2848
    ActiveWindow.ScrollRow = 2615
    ActiveWindow.ScrollRow = 1358
    ActiveWindow.ScrollRow = 730
    ActiveWindow.ScrollRow = 435
    ActiveWindow.ScrollRow = 311
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 1
Line2 =  ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:=Array( _
        "ACCEPTANCE", "CUSTOMIZATION", "DISASTER RECOVERY", "Enhancement", _
        "FinAssureProduct", "IMPLEMENTATION", "Implementation-Support", "INTERNAL", "L2", _
        "L2 Production Support", "L2 Support", "Live", "ONSITE", "Regression", "Simulation", _
        "SIT", "TEST", "Testing", "UAT", "WARRANTY"), Operator:=xlFilterValues
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False

i have made changes like this


at Line 1 i made changes like below , because i want to select only "Production" from the drop down list remaining all should be unchecked
Code: 
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
        "PRODUCTION"

at Line 2 i made changes like below, because i want uncheck Production and remaining all should be checked

Code: 
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:="<>Production", Operator:=xlFilterValues

if you check the Line 1code ActiveSheet.Range mentioned twice at starting and ending but in my code i made only one change as per above , please check and correct
 
Last edited:
Now entire code looks smothing like this i have devided in 5 parts as i am not able to paste entire code and from my network i cant upload anything.

Code: 
' Line1: Sub MacroCRM()
'
' MacroCRM Macro
'

'
    Cells.Select
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlLTR
        .MergeCells = False
    End With
    Cells.EntireColumn.AutoFit
    Columns("A:A").Select
    ActiveWindow.SmallScroll ToRight:=7
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "M"
    ActiveCell.FormulaR1C1 = "S+R:ULA % Agreed"
    Range("R2").Select
    Columns("R:R").EntireColumn.AutoFit
    Columns("Q:Q").EntireColumn.AutoFit
    Columns("R:U").Select
    Selection.Delete Shift:=xlToLeft
    Columns("T:AA").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]-RC[2]"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M4716")
    Range("M2:M4716").Select
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("I:I").Select
    Selection.NumberFormat = "[$-en-US]d-mmm-yy;@"
    Columns("J:J").Select
    Selection.NumberFormat = "[$-en-US]d-mmm-yy;@"
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AE$4716").AutoFilter Field:=5, Criteria1:="<>FINCRM 10.3.*", _
        Operator:=xlFilterValues
    ActiveSheet.Range("$A$1:$AE$4716").AutoFilter Field:=5, Criteria1:="<>ITL*", _
        Operator:=xlAnd, Criteria2:="<>URALSIB*", Operator:=xlFilterValues
 
Code: 
' Line2:
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Main").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("A1").Select
    ActiveSheet.Paste
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
        "PRODUCTION"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Production Calls").Select
    ActiveSheet.Paste
    Sheets("Main").Select
    ActiveWindow.ScrollRow = 3314
    ActiveWindow.ScrollRow = 2848
    ActiveWindow.ScrollRow = 2615
    ActiveWindow.ScrollRow = 1358
    ActiveWindow.ScrollRow = 730
    ActiveWindow.ScrollRow = 435
    ActiveWindow.ScrollRow = 311
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 1
    ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:="<>Production", Operator:=xlFilterValues
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Non-Production calls").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("I11").Select
    ActiveWindow.SmallScroll Down:=-9
    Sheets("Production Calls").Select
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=13, Criteria1:=">=0", _
        Operator:=xlAnd, Criteria2:="<=120"
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=11, Criteria1:=Array( _
        "WIP", "With Assignee", "With CCB Approver"), Operator:=xlFilterValues
[code]
 
Code: 
' line3:  
Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("CRM Calls Expiring in 5 days").Select
    ActiveWindow.SmallScroll Down:=-63
    Range("A3").Select
    ActiveSheet.Paste
    Sheets("Production Calls").Select
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=11, Criteria1:= _
        "With Requestor For Clarification"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CRM Calls Expiring in 5 days").Select
    ActiveWindow.SmallScroll Down:=33
    Range("A49").Select
    ActiveSheet.Paste
    Sheets("Non-Production calls").Select
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=13, Criteria1:=">=0", _
        Operator:=xlAnd, Criteria2:="<=120"
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=11, Criteria1:=Array( _
        "WIP", "With Assignee", "With CCB Approver"), Operator:=xlFilterValues
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("CRM Calls Expiring in 5 days").Select
    ActiveWindow.SmallScroll Down:=57
    Range("A102").Select
    ActiveSheet.Paste
    Sheets("Non-Production calls").Select
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=11, Criteria1:= _
        "With Requestor For Clarification"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CRM Calls Expiring in 5 days").Select
    ActiveWindow.SmallScroll Down:=66
    Range("A168").Select
    ActiveSheet.Paste
    Sheets("Production Calls").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=13, Criteria1:=">=0", _
        Operator:=xlAnd, Criteria2:="<=48"
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=11, Criteria1:="=WIP", _
        Operator:=xlOr, Criteria2:="=With Assignee"
 
Code: 
' Line4:    
Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Calls Breaching in 48 hours").Select
    ActiveWindow.SmallScroll Down:=-33
    Range("A3").Select
    ActiveSheet.Paste
    Sheets("Production Calls").Select
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=11, Criteria1:= _
        "With Requestor For Clarification"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Calls Breaching in 48 hours").Select
    ActiveWindow.SmallScroll Down:=18
    Range("A29").Select
    ActiveSheet.Paste
    Sheets("Non-Production calls").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=13, Criteria1:=">=0", _
        Operator:=xlAnd, Criteria2:="<=48"
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=11, Criteria1:="=WIP", _
        Operator:=xlOr, Criteria2:="=With Assignee"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Calls Breaching in 48 hours").Select
    ActiveWindow.SmallScroll Down:=30
    Range("A60").Select
    ActiveSheet.Paste
    Sheets("Non-Production calls").Select
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=11, Criteria1:= _
        "With Requestor For Clarification"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Calls Breaching in 48 hours").Select
    ActiveWindow.SmallScroll Down:=33
    Range("A92").Select
    ActiveSheet.Paste
    Sheets("Production Calls").Select
    ActiveWindow.SmallScroll Down:=-9
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=11, Criteria1:= _
        "With CCB Approver"
[code]
 
Code: 
' Line5: 
   Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("With CCB Approver-P").Select
    ActiveWindow.SmallScroll Down:=-42
    Range("A3").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollWorkbookTabs Sheets:=-5
    Sheets("Non-Production calls").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=11, Criteria1:= _
        "With CCB Approver"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("With CCB Approver- NP").Select
    ActiveWindow.SmallScroll Down:=-57
    Range("A3").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollWorkbookTabs Sheets:=-4
    ActiveWindow.ScrollWorkbookTabs Sheets:=-4
    Sheets("Production Calls").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=13, Criteria1:=">=0", _
        Operator:=xlAnd, Criteria2:="<=24"
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=11, Criteria1:=Array( _
        "WIP", "With Assignee", "With Requestor For Clarification", " WIP", "With CCB Approver", " With CCB Approver After Patch Initiation", "With Reviewer For Clarification", " Initiation", "With Support Team for Ownership", "With Release Manager Team For RCD Approval", "With Reviewer For Patch", "With Reviewer For Closure"), Operator:= _
        xlFilterValues
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Calls Breached Today-P").Select
    ActiveWindow.SmallScroll Down:=-111
    Range("A3").Select
    ActiveSheet.Paste
    Sheets("Non-Production calls").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=13, Criteria1:=">=0", _
        Operator:=xlAnd, Criteria2:="<=24"
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=11, Criteria1:=Array( _
        "WIP", "With Assignee", "With Requestor For Clarification", " WIP", "With CCB Approver", " With CCB Approver After Patch Initiation", "With Reviewer For Clarification", " Initiation", "With Support Team for Ownership", "With Release Manager Team For RCD Approval", "With Reviewer For Patch", "With Reviewer For Closure"), Operator:= _
        xlFilterValues
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Calls Breached Today-NP").Select
    Range("A3").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollWorkbookTabs Sheets:=-3
    Sheets("Production Calls").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1252").AutoFilter Field:=17, Criteria1:= _
        "Not Met"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("CRM Breached Calls-prod").Select
    ActiveWindow.SmallScroll Down:=-33
    ActiveWindow.ScrollRow = 182
    ActiveWindow.ScrollRow = 179
    ActiveWindow.ScrollRow = 176
    ActiveWindow.ScrollRow = 174
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 142
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 1
    Range("A3").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollWorkbookTabs Sheets:=-5
    Sheets("Non-Production calls").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$1912").AutoFilter Field:=17, Criteria1:= _
        "Not Met"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Calls Breached Today-NP").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("CRM Breached calls-non prod").Select
    ActiveWindow.SmallScroll Down:=-60
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("A3").Select
    ActiveSheet.Paste
    Range("D8").Select
End Sub
 
Hi Pcosta,
i have made changes and its working but as you informed previously

""Since you can have more/less data, instead of "ActiveSheet.Range("$A$1:$AE$4716")...", you can use "ActiveSheet.Range("A:AE")..."""

above is not working for me i have made changes accordingly ... its giving error

and also can you check in reply no 32

ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
"PRODUCTION"

is it correct what i have mentioned , because i am getting error here.

upload_2017-3-2_14-17-27.png

in this drop down i want to select only "production" from the list remaining all should be unchecked

and at other please i want to deselect only "production" and select all remaining.
 
Code: 
Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=19, Criteria1:=Array( _
        "ALL OTHERS", "AMLA", "APP-ISSUE", "CHANGE REQUEST", "CLARIFICATION", _
        "CLARIFICATION_SOUGHT", "Clarifications-Due to Finacle Knowledge", "Code Review", _
        "Coding Error", "CR", "Critical Patches", "CUSTOMISATION_DESIGN", _
        "CUSTOMISATION_PERFORMANCE", "CUSTOMIZATION", _
        "Customization-Interfaces Issues-Design Issue", "DATA MIGRATION", _
        "Data Migration Error", "Data Related Issues-Data Mapping Issue", _
        "Data Related Issues-Incomplete Data", "Data Related Issues-Wrong Data", _
        "Deployment Error", "Design Error", "DESIGN ISSUES", "DOCUMENTATION", "DUPLICATE", _
        "Enhancement", "EOD/BOD", "Essential Patches", "IMPLEMENTATION", _
        "IMPLEMENTATION OF NEW PRODUCTS", "INFRA_REQMTS", "Infrastructure Error", _
        "INTERFACE", "Invalid Requirement", "L2 Production suport", "Localization", _
        "NEW REQUIREMENT", "NOT AN ISSUE", "OBSERVATION", "OPERATING SYSTEM", "OTHERS", _
        "Parameter Error", "PARAMETERIZATION ISSUES", "PERFORMANCE", "Porting", _
        "PRODUCT BUG", "Product Bug-Bug in the Product", _
        "Product Bug-Product Enhancement", "Product Dependency", "Product Feature", _
        "Production", "Recon", "Regression Testing", "REPORT", "SET UP ISSUES", "SIT_BUG", _
        "SOFTWARE ENHANCEMENT REQUEST", "SOFTWARE ERROR/TROUBLE FIX REQUEST", _
        "Technical Issue-Configuration Issue", "Test", "Third party issue", "TRAINING", _
        "UAT_BUG", "USER_MISTAKE", "WARRANTY", "WAS", "="), Operator:=xlFilterValues
    ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
        "PRODUCTION"

here how it looks but i deleted and kept only

ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:="PRODUCTION"

is this right
 
narsing rao said:
Hi Pcosta,
i have made changes and its working but as you informed previously

""Since you can have more/less data, instead of "ActiveSheet.Range("$A$1:$AE$4716")...", you can use "ActiveSheet.Range("A:AE")..."""

above is not working for me i have made changes accordingly ... its giving error
Click to expand...

This should work:
Code: 
    ActiveSheet.Range("A:AE").AutoFilter Field:=2, Criteria1:="<>ITL*", _
            Operator:=xlAnd, Criteria2:="<>URALSIB*"

Is this what you have?

ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
"PRODUCTION"

is it correct what i have mentioned , because i am getting error here.
Click to expand...

I can't see anything wrong with it... you must have the correct sheet active though, since you are using "ActiveSheet" and not referencing the sheet by index number or name.

in this drop down i want to select only "production" from the list remaining all should be unchecked
Click to expand...

Code: 
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
        "PRODUCTION"
does exactly that!

and at other please i want to deselect only "production" and select all remaining
Click to expand...

Use
Code: 
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
        "<>PRODUCTION"
 
please check this i am getting error

"application defined error or object defined error"

upload_2017-3-2_16-16-31.png

i tried using Array that is also giving error..
 
Hi,

As far as I know you can only use "Operator:=xlAnd" once which means you are limited to 2 criteria.
For more criteria you must use an array but that is also limited as you can't use "<>".
Even using advanced filters has the same limitation.

Perhaps the best solution is to go through all rows in the range and copy only if all conditions are met.
This can be achieved trough a combination of a loop and offset statements.
 
Hi Pcosta,

i think i found one way to do this...first i declared
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=19, Criteria1:="<>CUSTOMISATION REQUEST"

at the starting of the code were we are filtering the versions and next i declared

ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=19, Criteria1:="<>CUSTOMIZATION_ISSUE", _
Operator:=xlAnd, Criteria2:="<>SIT_CUSTOMISATION"

at the place where we are filtering "production" data
after doing this i ran the code and i got report correct and checked the data i didn't found any thing with above criteria.

let me know if i did it correct or not.
 
Filters are not cumulative through VBA, which means that the second time you filter you are in fact replacing the previous criteria.

It may have worked in this specific instance but it isn't the same as <>CUSTOMISATION REQUEST & <>CUSTOMIZATION_ISSUE & <>SIT_CUSTOMISATION.

You either just need the last 2 criteria or this time there was no "CUSTOMISATION_REQUEST" to be excluded.

In any case, you can keep it the way it is but I wouldn't trust the macro's resulting report blindly just yet.
 
I agree with you and don't want to go blindly..so now what i will do is ..i will add all the categories in the code without missing any thing and exclude CUSTOMISATION REQUEST, CUSTOMIZATION_ISSUE & SIT_CUSTOMISATION.

i think in this way i can make sure in the future there wont be any data mismatch.

let me know if i am right..

and as you said in the previous post "This can be achieved trough a combination of a loop and offset statements"

can you provide me the code how to achieve this.

in future it will be very useful for me.
 
Hi Pcosta,

can i make changes to range fields as below to make it dynamic

before change:
Code: 
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=19

after change
Code: 
 Sheets("main").Range("A:AS").AutoFilter Field:=19

or how to declare the range in below code:
Code: 
Sub Offset2()
 Range("A1").End(xlDown).Offset(1, 0).Select
End Sub

please let me know
 
narsing rao said:
I agree with you and don't want to go blindly..so now what i will do is ..i will add all the categories in the code without missing any thing and exclude CUSTOMISATION REQUEST, CUSTOMIZATION_ISSUE & SIT_CUSTOMISATION.

i think in this way i can make sure in the future there wont be any data mismatch.

let me know if i am right...
Click to expand...

Hi,

You are thinking correctly, but the problem still remains: How to exclude those 3 from the filter? I still haven't found a way to do it without knowing the full array of criteria.

and as you said in the previous post "This can be achieved trough a combination of a loop and offset statements"

can you provide me the code how to achieve this.

in future it will be very useful for me
Click to expand...

Please refer to attached... in the sample, it will only copy the rows that simultaneously have "1" in the columns C, E and G
I hope this is comprehensive enough.
 

Attachments

  • Test.xlsm
    17.7 KB · Views: 5
narsing rao said:
Hi Pcosta,

can i make changes to range fields as below to make it dynamic

before change:
Code: 
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=19

after change
Code: 
Sheets("main").Range("A:AS").AutoFilter Field:=19

or how to declare the range in below code:
Code: 
Sub Offset2()
Range("A1").End(xlDown).Offset(1, 0).Select
End Sub

please let me know
Click to expand...

So, replacing "Range("$A$1:$S$3416")" with Range("A:S") (not A:AS) doesn't really make it dynamic, what it does is consider the whole columns in the autofilter which, in this case, is basically the same thing so I guess we can say it is dynamic as you aren't defining any end row for the range.

Now, to specifically select the same range ("$A$1:$S$3416") but in a dynamic way, you can do the following (there are plenty of different ways to do it, this is just one of them):
Code: 
Sub DynRngSelect()

    Dim lrow As Integer 'Define lrow as an Integer variable (whole number) to store the last used row
    lrow = Cells(Rows.Count, "S").End(xlUp).Row 'Find the last row and store it in lrow using the column "S"
    Range("A1:S" & lrow).Select 'Select the range from "A1" to "S" and lrow so it will be "A1:S10" if last row is 10
    
End Sub

Your "Range("A1").End(xlDown).Offset(1, 0).Select" just selects the first empty cell in the column A after "A1"... for instance, if you have a gap (let's say A10 is empty) and there are values up to A99, this will select A10 and not A100.
This is why I do it from bottom to top "cells(rows.count, "A").End(xlup)" so it will never stop in a Gap and correctly locate the last used row.
 
You must log in or register to reply here.
Back
Top