Extract Data From Specific Columns That Meet Multiple Criteria

[bradyman97]

I have a sheet called Invoices below are the name of the columns. How can I extract data from columns E, G, K, L, D with criteria from column A and column B. Everytime I extract the data I need it go at the end of the previous data extracted. If it helps I have this advanced filtered VBA code below but it doesn't put it at the end of the previous month. Any help would be greatly appreciated.




A - Month/Year
B - Location
C - Vendor
D - PO #
E - PO Date
F - Line #
G - Vendor Item #
H - Description
I - Pack Description
J - Purchase Unit
K - Quantity
L - Price
M - Ext Amount
N - Notes



Sub Copy_Warehouse_Invoices() 'Module7


Dim rg As Range
Set rg = ThisWorkbook.Worksheets("Warehouse_Invoices").Range("A7").CurrentRegion

rg.Offset(1).ClearContents

Dim rgInvoices As Range, rgCriteria As Range, rgWarehouse_Invoices As Range


Set rgInvoices = ThisWorkbook.Worksheets("Invoices").Range("A1").CurrentRegion

Set rgCriteria = ThisWorkbook.Worksheets("Warehouse_Invoices").Range("A1").CurrentRegion

Set rgWarehouse_Invoices = ThisWorkbook.Worksheets("Warehouse_Invoices").Range("A7").CurrentRegion

rgInvoices.AdvancedFilter xlFilterCopy, rgCriteria, rgWarehouse_Invoices

Range("A7").CurrentRegion.Sort _
key1:=Range("B7"), order1:=xlAscending, _
key2:=Range("A7"), order2:=xlAscending, Header:=xlYes


Columns("A:E").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

End With

With Selection.Font
.Name = "Arial"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

Range("G7").Select


End Sub

 

[mart37]

The advanced filter replace the old value with the new values.
You can copy first the old values to a new tabel (copy-paste).
Or you can change your criteria incluse the previous selection. (2/2021 -> 2/2021 or 1/2021)

 

[bradyman97]

Say I already have filtered my data for January. I'm looking for a VBA code that when I change the criteria to February 2021 I would like to have put at the end of January 2021 data

 

[GlennUK]

Advanced Filter doesn't work that way. Filter it somewhere else, then copy those results onto the end of your already existing subset.