Excel 2007-Adv Filter Copy and Loop with Nested Autofilter Using Values from Adv Filter

Hi, Southfish,

the given code will copy all found values over, not as per your request only the latest one.

Please try:

Sub Truck_AdvFltr_AutoFltr_Copy()

Dim TruckListCount As Long
Dim Truckname As String
Dim LRTruckTable As Long
Dim TruckDate As String, TruckTime As String, LRTruck As Long, CopyTruck As Range
Dim LRTruck2 As Long

'Advanced Filter

With Sheets("Truck")
  .Range(.Range("B1"), .Range("B2").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, _
      CopyToRange:=Sheets("TruckFiltered").Range("A1"), _
      unique:=True
End With
'Loop

LRTruckTable = Worksheets("TruckFiltered").Range("A" & Rows.Count).End(xlUp).Row

For TruckListCount = 2 To LRTruckTable
  Truckname = Worksheets("TruckFiltered").Range("A" & TruckListCount)

'AutoFilter
  
  
  With Worksheets("Truck")
  
  If .FilterMode Then .ShowAllData
  With .Range("A1").CurrentRegion

   .AutoFilter Field:=2, Criteria1:=Truckname
                               
   TruckDate = Format(Application.Subtotal(4, .Range("A:A")), .Range("A2").NumberFormat)
   .AutoFilter Field:=1, Criteria1:=TruckDate
   
   TruckTime = Format(Application.Subtotal(4, .Range("G:G")), .Range("G2").NumberFormat)
   .AutoFilter Field:=7, Criteria1:=TruckTime
   
  End With
'Copy to destination

  LRTruck2 = .Range("C" & Rows.Count).End(xlUp).Row
  .Range("C" & LRTruck2).Copy _
      Destination:=Sheets("TruckFiltered").Range("B" & TruckListCount)
  
  .AutoFilterMode = False
  
  End With

Next TruckListCount
End Sub

If you really have only one cell you want to pass you could assign the value directly instead of using Copy which would bring over formatting as well.

Ciao,
Holger