VBA macro with advanced filtering

[altaris]

Hi all,

I am using the below code to try to perform the following actions
1. From the bidding workbook execute the macro
2. Macro opens a database file (AllData)
3. Macro is supposed to filter this database file by using a criteria defined in the bidding workbook (cell C3, sheet Summary)
4. Macro pastes filtered output in workbook Vendor_Code_Data

The issue I am facing is that the filtering is not executing for some reason, it copies and pastes the entire file.

I tried to look into the value that the variable takes but could not find it. Would someone have an idea?

Thanks a lot for your help

_______________________



Sub data_manipulation()

Application.ScreenUpdating = False

Dim AllData As String
Dim VendorFile As String
Dim Bidding As String
Dim FilterRange As Range, Criteria As Range, TargetRange As Range
Dim WB_Data As Workbook, WB_Vendor As Workbook, WB_Bidding As Workbook
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet


AllData = "results.xls"
VendorFile = "Vendor_Code_Data.xlsx" '->>>>> name to adapt'
Bidding = "201804_Bidding.xlsm"

Set WB_Bidding = ThisWorkbook
Set WB_Vendor = Workbooks.Open(ActiveWorkbook.Path & "\" & VendorFile)
Set WB_Data = Workbooks.Open(ActiveWorkbook.Path & "\" & AllData)


If WB_Vendor Is Nothing Then
MsgBox "You must have your Target WorkBook Open."
Exit Sub
End If

' ---- Assign WorkSheet Variables
Set WS1 = WB_Data.Worksheets("results")
Set WS2 = WB_Vendor.Worksheets("Vendor_Code_Data")
Set WS3 = WB_Bidding.Worksheets("Summary")

' ---- Clear Target range of old Data
WS2.UsedRange.ClearContents

' ---- Set variables to specific Ranges

Set FilterRange = WS1.Range("A1").CurrentRegion
Set Criteria = WS3.Range("C3").CurrentRegion
Set TargetRange = WS2.Range("A1")


' ---- Do Advanced Filter using variables

FilterRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=TargetRange, CriteriaRange:=Criteria

Application.ScreenUpdating = False

End Sub

[JLGWhiz]

Can you attach your worksheets? Use the facility provided by clicking the 'Go Advanced' button. The toolbar paper clip does not work.