Hi,
Wondering if it's possible to filter 2 different columns in the same sheet with an and/or returning records that may not be related, then paste the data to a new csv file.
Or maybe it might be better to change the code to 2 separate sub's so after the first sub run's and creates a .csv file the second sub adds the returned data to the bottom of the sheet at the end of the data from
code:
Dim dicCriteria As Object
Dim vData As Variant
Dim i As Long
Dim main As Workbook
Set dicCriteria = CreateObject("Scripting.Dictionary")
dicCriteria.CompareMode = 1 'vbTextCompare
Set main = ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ChDir Application.ActiveWorkbook.Path
Workbooks.Open Filename:="FILE_NAME.csv"
With Sheets("TASK_SIGNOFF_REPORT").Range("A1:V" & Sheets("TASK_SIGNOFF_REPORT").Range("A" & Rows.Count).End(xlUp).Row)
vData = .Columns(7).Cells.Value
For i = 2 To UBound(vData, 1) 'exclude headers and start from the second row of data
If Not dicCriteria.Exists(vData(i, 1)) Then
Select Case True
Case vData(i, 1) Like "GG018*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GG019*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GD430*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GG812*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GG700*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GE981*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GE982*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GF002*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GF748*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GG802*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GH937*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GH946*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GD429*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GF217*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GH938*"
dicCriteria(vData(i, 1)) = ""
End Select
End If
Next i
If dicCriteria.Count > 0 Then
.AutoFilter field:=7, Criteria1:=dicCriteria.Keys, Operator:=xlFilterValues
.AutoFilter field:=4, Criteria2:=Array("MANAGERS_NAME"), Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = True
ActiveWorkbook.SaveAs Filename:= _
"FIL_NAME", FileFormat:=CSV
Else
MsgBox "No records found.", vbInformation
End If
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Wondering if it's possible to filter 2 different columns in the same sheet with an and/or returning records that may not be related, then paste the data to a new csv file.
Or maybe it might be better to change the code to 2 separate sub's so after the first sub run's and creates a .csv file the second sub adds the returned data to the bottom of the sheet at the end of the data from
code:
Dim dicCriteria As Object
Dim vData As Variant
Dim i As Long
Dim main As Workbook
Set dicCriteria = CreateObject("Scripting.Dictionary")
dicCriteria.CompareMode = 1 'vbTextCompare
Set main = ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ChDir Application.ActiveWorkbook.Path
Workbooks.Open Filename:="FILE_NAME.csv"
With Sheets("TASK_SIGNOFF_REPORT").Range("A1:V" & Sheets("TASK_SIGNOFF_REPORT").Range("A" & Rows.Count).End(xlUp).Row)
vData = .Columns(7).Cells.Value
For i = 2 To UBound(vData, 1) 'exclude headers and start from the second row of data
If Not dicCriteria.Exists(vData(i, 1)) Then
Select Case True
Case vData(i, 1) Like "GG018*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GG019*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GD430*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GG812*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GG700*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GE981*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GE982*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GF002*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GF748*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GG802*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GH937*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GH946*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GD429*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GF217*"
dicCriteria(vData(i, 1)) = ""
Case vData(i, 1) Like "GH938*"
dicCriteria(vData(i, 1)) = ""
End Select
End If
Next i
If dicCriteria.Count > 0 Then
.AutoFilter field:=7, Criteria1:=dicCriteria.Keys, Operator:=xlFilterValues
.AutoFilter field:=4, Criteria2:=Array("MANAGERS_NAME"), Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = True
ActiveWorkbook.SaveAs Filename:= _
"FIL_NAME", FileFormat:=CSV
Else
MsgBox "No records found.", vbInformation
End If
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic