narsing rao
Member
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??.
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
ActiveSheet.Range("$A$1:$AE$4716").AutoFilter Field:=5, Criteria1:="<>FINCRM 10.3.*", _
Operator:=xlFilterValues
Since you can have more/less data, instead of "ActiveSheet.Range("$A$1:$AE$4716")...", you can use "ActiveSheet.Range("A:AE")..."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..
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
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
"PRODUCTION"
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:="<>Production", Operator:=xlFilterValues
' 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
' 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]
' 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"
' 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]
' 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,after running above code i am getting ERROR
Run-time error '1004'
AUTOFILTER METHOD OF RANGE CLASS FAILED
View attachment 39150
ActiveSheet.Range("$A$1:$AE$4716").AutoFilter Field:=2, Criteria1:="<>ITL*", _
Operator:=xlAnd, Criteria2:="<>URALSIB*"
Hi,
What you want to use there is:
Code:ActiveSheet.Range("$A$1:$AE$4716").AutoFilter Field:=2, Criteria1:="<>ITL*", _ Operator:=xlAnd, Criteria2:="<>URALSIB*"
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"
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
ActiveSheet.Range("A:AE").AutoFilter Field:=2, Criteria1:="<>ITL*", _
Operator:=xlAnd, Criteria2:="<>URALSIB*"
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
"PRODUCTION"
is it correct what i have mentioned , because i am getting error here.
in this drop down i want to select only "production" from the list remaining all should be unchecked
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
"PRODUCTION"
and at other please i want to deselect only "production" and select all remaining
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=18, Criteria1:= _
"<>PRODUCTION"
ActiveSheet.Range("$A$1:$S$3416").AutoFilter Field:=19
Sheets("main").Range("A:AS").AutoFilter Field:=19
Sub Offset2()
Range("A1").End(xlDown).Offset(1, 0).Select
End Sub
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
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