aravindhan_3
03-31-2016, 05:00 AM
Hi,
I am using this main macro to the loop through all files in a folder
sub main()
xxx..
xxx...
call Filter_Country ' calling a separate macro
end sub
and filter country macro is below
Sub filter_country()
Application.ScreenUpdating = False
If Sheets("Master Data").Visible = False Then Sheets("Master Data").Visible = True
If Sheets("Raw Data").Visible = False Then Sheets("Raw Data").Visible = True
Application.ScreenUpdating = False
Sheets("Raw Data").Range("D4:T100000").ClearContents
Sheets("Raw Data").Range("U5:At100000").Clear Sheets("Master Data").Select
If ActiveSheet.AutoFilterMode = False Then Rows("$1:$1").AutoFilter
Range("D1").Select
Dim Crit As String
Crit = Worksheets("Working").Range("C2").Value
Dim Lr As Long
Lr = Worksheets("Master Data").Range("D" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("$A$1:$T" & Lr).AutoFilter Field:=7, Criteria1:=Crit
ActiveSheet.Range("$A$1:$T$" & Lr).AutoFilter Field:=12, Criteria1:=Array( _
"05-Senior Manager", "06-Senior Manager", "07-Manager", "08-Consultant", _
"09-Consultant", "10-Analyst", "11-Analyst", "12-Associate", "13-Associate"), _
Operator:=xlFilterValues
Range("D2:T" & Lr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
.....
...
..
etc
All these macros are working fine if there is a result in the filterd data, it stops when there is no data in the result set , getting error here
Range("D2:T" & Lr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
can someone help me to sort this, if no result or error, then stop the further steps in this file and move to the next file in the loop.
I tries on error resume next, but not sure where to use it..
Regards
Arvind
I am using this main macro to the loop through all files in a folder
sub main()
xxx..
xxx...
call Filter_Country ' calling a separate macro
end sub
and filter country macro is below
Sub filter_country()
Application.ScreenUpdating = False
If Sheets("Master Data").Visible = False Then Sheets("Master Data").Visible = True
If Sheets("Raw Data").Visible = False Then Sheets("Raw Data").Visible = True
Application.ScreenUpdating = False
Sheets("Raw Data").Range("D4:T100000").ClearContents
Sheets("Raw Data").Range("U5:At100000").Clear Sheets("Master Data").Select
If ActiveSheet.AutoFilterMode = False Then Rows("$1:$1").AutoFilter
Range("D1").Select
Dim Crit As String
Crit = Worksheets("Working").Range("C2").Value
Dim Lr As Long
Lr = Worksheets("Master Data").Range("D" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("$A$1:$T" & Lr).AutoFilter Field:=7, Criteria1:=Crit
ActiveSheet.Range("$A$1:$T$" & Lr).AutoFilter Field:=12, Criteria1:=Array( _
"05-Senior Manager", "06-Senior Manager", "07-Manager", "08-Consultant", _
"09-Consultant", "10-Analyst", "11-Analyst", "12-Associate", "13-Associate"), _
Operator:=xlFilterValues
Range("D2:T" & Lr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
.....
...
..
etc
All these macros are working fine if there is a result in the filterd data, it stops when there is no data in the result set , getting error here
Range("D2:T" & Lr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
can someone help me to sort this, if no result or error, then stop the further steps in this file and move to the next file in the loop.
I tries on error resume next, but not sure where to use it..
Regards
Arvind