Please help with VBA code for filtering data?

[msb1977]

Hi all

The below code works, but the arrays for filtering data will only accept two variables to filter on. Am I doing something wrong? I would love to be able to put all 4 variables in the array and get rid of the excess code.

Please refer to the sections below: 'Filter data on 1002 Based SOF workbook (part 1) & (part 2)

Thanks in advance for the help!

Sub FilterCopySOF()
  
'Define variables
Dim wbBudget    As Workbook  'Budget & Execution Tool workbook
Dim wbSOF       As Workbook  '1002_Based_SOF workbook located in MDA Daily Reports
Dim LastDate    As Date      'Last modified date of 1002_Based_SOF
Dim LastRow     As Long      'Last row of SOF DAI worksheet in the GM 1-N Budget & Execution Tool workbook
  
'Delete old data from SOF DAI worksheet and turn off filter
Application.CutCopyMode = False
Set wbBudget = ActiveWorkbook
With wbBudget.Sheets("SOF DAI")
  .Range("A2", .Cells(.Rows.Count, .Columns.Count)).Clear
End With
wbBudget.Sheets("SOF DAI").AutoFilterMode = False
   
'Open and Activate the 1002 Based SOF from the server
Set wbSOF = Workbooks.Open("xxx/1002_Based_SoF.xlsx")
wbSOF.Activate

'Retrieve Date of 1002 Based SOF
LastDate = FileDateTime("xxx/1002_Based_SoF.xlsx")
wbBudget.Sheets("Report Set-Up").Range("Date_SOF").Value = LastDate

'Filter data on 1002 Based SOF workbook (part 1)
wbSOF.Sheets(1).Select
Range("A1").AutoFilter
Range("A1").AutoFilter Field:=2, Criteria1:=Array("2015", "2016"), Operator:=xlFilterValues
Range("A1").AutoFilter Field:=3, Criteria1:=Array("=2P_SO08*", "=X3_*"), Operator:=xlFilterValues
   
'Copy and Paste the 1002 Based SOF (part 1)
Application.CutCopyMode = False
wbSOF.Sheets(1).Range("Table1").SpecialCells(xlCellTypeVisible).Copy
wbBudget.Sheets("SOF DAI").Range("A2").PasteSpecial xlPasteAll
Application.CutCopyMode = False
   
'Filter data on 1002 Based SOF workbook (part 2)
wbSOF.Sheets(1).AutoFilterMode = False
wbSOF.Sheets(1).Select
Range("A1").AutoFilter
Range("A1").AutoFilter Field:=2, Criteria1:=Array("2015", "2016"), Operator:=xlFilterValues
Range("A1").AutoFilter Field:=3, Criteria1:=Array("=S3_*", "=Y3_*"), Operator:=xlFilterValues
   
'Copy, Paste, and Close the 1002 Based SOF (part 2)
wbSOF.Sheets(1).Range("Table1").SpecialCells(xlCellTypeVisible).Copy
With wbBudget.Sheets("SOF DAI")
  LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
wbBudget.Sheets("SOF DAI").Range("A" & LastRow + 1).PasteSpecial xlPasteAll
Application.CutCopyMode = False
wbSOF.Close SaveChanges:=False
   
'Activate Budget Workbook again and turn on filter
wbBudget.Activate
If Not wbBudget.Sheets("SOF DAI").AutoFilterMode Then
  wbBudget.Sheets("SOF DAI").Range("A1").AutoFilter
End If

'Go to SOF Report Page and prompt user to validate data
wbBudget.Sheets("SOF Rpt").Select
wbBudget.Sheets("SOF Rpt").Range("A1").Select
wbBudget.Sheets("SOF Rpt").Range("A1").Activate
Dim Output As Integer
Output = msgbox("Please verify error checking." & vbCrLf & _
                "" & vbCrLf & _
                "All deltas in cells D2:I3 should equal zero." & vbCrLf & _
                "" & vbCrLf & _
                "If there is an error, refer to the User Manual for help.", vbOKOnly, "Validate Data")
      
'Clear Memory
Set wbSOF = Nothing
Set wbBudget = Nothing
    
End Sub

 

[Worf]

Hi

See if this example helps. Tell me if you need more detailed explanations.

Excel Workbook
	A	B	C	D	E	F
1	Customer ID	Year	Product Code		Year	Product Code
2	c1	2012	2P_SO081		2015	2P_SO08*
3	c2	2013	2P_SO082		2016	X3_*
4	c3	2015	3PU
5	c4	2015	2P_SO084
6	c5	2016	2P_SO085
7	c6	2016	X3_1
8	c7	2016	X3_2
9	c8	2019	X3_3
10	c9	2020	X3_4
Customers

Sub AdvFilt()
[a1:c10].AdvancedFilter xlFilterInPlace, [e1:f3], , 0
End Sub

 

[msb1977]

Thanks for the response! I was able to replicate what you did and it worked. However, I couldn't get it to work on my dataset. I wonder if the data being in a table is causing problems.

 

[Worf]

Can you post the table's first ten lines? Or post a link to the worksheet? Or email it to me?

A table per se should not be a problem.