Hello Experts,
Requesting you to review my macro. i have tried little bit and i am new to this so please help me out. I want to save the file into two different format based on criteria that i have done. but while filtering if no data for that respective criteria then it has to move to next filter option. kindly help.
below are the codes.
ThisWorkbook.Sheets("Upload Template").Activate
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Range("B1").Select
ActiveSheet.Range("$A$1:$M$334").AutoFilter Field:=2, Criteria1:= _
"Lockbox-PNC-677971B"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location"
ActiveWorkbook.SaveAs Filename:= _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location\Lockboxpnc677971.csv" _
, FileFormat:=xlCSVUTF8, CreateBackup:=False
Range("I8").Select
ActiveWorkbook.SaveAs Filename:= _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location\Lockboxpnc677971.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range("B39").Select
Selection.End(xlUp).Select
ActiveSheet.Range("$A$1:$M$334").AutoFilter Field:=2, Criteria1:= _
"Lockbox-PNC-772863"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location\Lockboxpnc772863.csv" _
, FileFormat:=xlCSVUTF8, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:= _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location\Lockboxpnc772863.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
ActiveSheet.Range("$A$1:$M$334").AutoFilter Field:=2, Criteria1:= _
"Lockbox-PNC-775139"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Requesting you to review my macro. i have tried little bit and i am new to this so please help me out. I want to save the file into two different format based on criteria that i have done. but while filtering if no data for that respective criteria then it has to move to next filter option. kindly help.
below are the codes.
ThisWorkbook.Sheets("Upload Template").Activate
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Range("B1").Select
ActiveSheet.Range("$A$1:$M$334").AutoFilter Field:=2, Criteria1:= _
"Lockbox-PNC-677971B"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location"
ActiveWorkbook.SaveAs Filename:= _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location\Lockboxpnc677971.csv" _
, FileFormat:=xlCSVUTF8, CreateBackup:=False
Range("I8").Select
ActiveWorkbook.SaveAs Filename:= _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location\Lockboxpnc677971.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range("B39").Select
Selection.End(xlUp).Select
ActiveSheet.Range("$A$1:$M$334").AutoFilter Field:=2, Criteria1:= _
"Lockbox-PNC-772863"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location\Lockboxpnc772863.csv" _
, FileFormat:=xlCSVUTF8, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:= _
"\\Itinblr05sfps01\ifsc - ar\Operations Team\Susikaran-DBS\Macro File location\Lockboxpnc772863.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
ActiveSheet.Range("$A$1:$M$334").AutoFilter Field:=2, Criteria1:= _
"Lockbox-PNC-775139"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select