0

I've updated my code which now works as intended, however I would like set filterList1 and filterList2 equal to cell C5 and C6 respectively on my "Assumptions" worksheet. How can I do this?

I tried setting filterList1 = Worksheets("Assumptions").Cells(5, "C").Value and filterList2 = Worksheets("Assumptions").Cells(6, "C").Value but doing so seemed to break most lines in the code.

Any suggestions would be really helpful!

Sub Refresh_PlanIDs()

Dim filterList1 As Variant
    filterList1 = Array("2019")
    filterCol1 = 68 'or whatever column contains the IDs
Dim filterList2 As Variant
    filterList2 = Array("Travel")
    filterCol2 = 69 'or whatever column contains the names

Dim sourceWS As Worksheet
Set sourceWS = Sheets("report1603894994415")
Dim destinationWS As Worksheet
Set destinationWS = Sheets("Demand Summary by Plan (2)")

lastrowSrc = sourceWS.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
lastrowDest = destinationWS.Range("B" & Rows.Count).End(xlUp).Row

sourceWS.AutoFilterMode = False

sourceWS.Range("$A$1:$DA" & lastrowSrc).AutoFilter Field:=filterCol1, _
        Criteria1:=filterList1, Operator:=xlFilterValues
sourceWS.Range("$A$1:$DA" & lastrowSrc).AutoFilter Field:=filterCol2, _
        Criteria1:=filterList2, Operator:=xlFilterValues

sourceWS.Range("BH2:BH" & lastrowSrc).SpecialCells _
        (xlCellTypeVisible).Copy _
        Destination:=destinationWS.Cells(lastrowDest + 1, 2)

If sourceWS.AutoFilterMode Then sourceWS.ShowAllData

Dim MyRange As Range
Dim LastRow2 As Long

'Remove Duplicates
LastRow2 = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Set MyRange = ActiveSheet.Range("B10:B" & LastRow2)
MyRange.RemoveDuplicates Columns:=1, Header:=xlYes


End Sub
Coles
  • 25
  • 6
  • 2
    Use `Range.AutoFilter` or `Range.AdvancedFilter` instead of looping. – BigBen Dec 03 '20 at 15:07
  • 2
    [Use `Long`, not `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). – BigBen Dec 03 '20 at 15:08
  • `.Range("BH2:BH" & LastRow1).Copy ThisWorkbook.Worksheets("Demand Summary by Plan (2)").Range("B10")` copies the exact same range every time. `LastRow1` never changes. – BigBen Dec 03 '20 at 15:09
  • @BigBen I'm new to VBA, how should I adjust the code? – Coles Dec 03 '20 at 15:12
  • 2
    There are examples of how to use `Range.AutoFilter` here on SO, such as [this one](https://stackoverflow.com/questions/11903878/autofilter-macro-then-copy-visible-data-only-and-paste-to-next-available-row). – BigBen Dec 03 '20 at 15:14
  • I can recommend [this excellent article](https://excelmacromastery.com/excel-vba-copy/) about copying data with VBA by Paul Kelly (ExcelMacroMastery). You will find detailed descriptions of how to use `Range.AdvancedFilter` there. If you prefer following a video, he also has an equally great [video](https://www.youtube.com/watch?v=0YNhxVu2a5s) on the topic. – GWD Dec 03 '20 at 15:37
  • @BigBen can you take a look at my revised code/questions? – Coles Dec 03 '20 at 20:05
  • `filterList1 = Worksheets("Assumptions").Cells(5, "C").Value` should work. What specifically broke further on? – BigBen Dec 03 '20 at 20:38

0 Answers0