Stuck on Advanced Filter

[ianfrancis56]

Hi All,

I am really jammed up here and cannot figure this out. Essentially, I want to run the advanced filter, not filtering for unique results, the criteria being a referenced date. However, only one result is being returned as opposed to 4. Thank you for any help! The code is near the bottom, it is the For Each loop referencing the variable lcat.

Sub CreateInvoice()

    Dim NewInvoices As Workbook, cell As Range, last&
    
    With Workbooks("SD Invoice Automation").Sheets("Source")
        last = .Cells(.Cells(1, 8).End(xlDown).Row, 8).Row
        .Range("H1:H" & last).AdvancedFilter Action:=xlFilterInPlace, _
        Unique:=True
            
        For Each cell In .Range("H2:H" & last)
            NewInvoice.cbxStartDate.AddItem cell.Value
        Next cell
    
        For Each cell In .Range("I2:I" & last)
            NewInvoice.cbxEndDate.AddItem cell.Value
        Next cell
    End With
    
    Set NewInvoices = Workbooks.Add
    
    
    Workbooks("SD Invoice Automation").Sheets("Template").Range("A1:Z1000").Copy Destination _
    :=NewInvoices.Sheets(1).Range("A1")
    
    NewInvoice.Show
    
End Sub
Sub NewInvoices()
Dim scnum As Range, lcat As Range, i@, j@, k@, l@, last&, inv As Workbook, sheet As Worksheet

i = 1
j = 2
k = 26
l = 2

    With ActiveWorkbook.Sheets(1)
        .Cells(12, 11) = NewInvoice.cbxStartDate
        .Cells(12, 13) = NewInvoice.cbxEndDate
        ActiveWorkbook.SaveAs "Invoice #" & _
        Workbooks("SD Invoice Automation").Sheets("Source") _
        .Cells(2, 12).Value & ".xlsx"
        Set inv = ActiveWorkbook
    End With
    
    With Workbooks("SD Invoice Automation").Sheets("Source")
        Unload NewInvoice
        last = .Cells(.Cells(1, 8).End(xlDown).Row, 8).Row
        Unload NewInvoice
        .Range("H1:H" & last).AdvancedFilter Action:= _
        xlFilterInPlace, Unique:=True
        
        For Each scnum In .Range("A2:A" & last)
            With inv.Sheets(i)
                .Range("B1:N1").ColumnWidth = 10.71
                .Range("L:L,A:A,O:O").ColumnWidth = 0.75
                .Range("A1,A3,A7,A10,A13,A18,A19,A22,A25,A28,A30,A32,A34,A35,A38,A40").RowHeight = 7.5
                .Name = Workbooks("SD Invoice Automation") _
                .Sheets("Source").Cells(j, 1).Value
                .Cells(21, 2).Value = Workbooks("SD Invoice Automation") _
                .Sheets("Source").Cells(j, 1).Value
                .Cells(21, 5).Value = Workbooks("SD Invoice Automation") _
                .Sheets("Source").Cells(j, 2).Value
                .Cells(21, 8).Value = Workbooks("SD Invoice Automation") _
                .Sheets("Source").Cells(j, 3).Value
                .Cells(9, 13) = Date
                .Cells(9, 14) = Workbooks("SD Invoice Automation") _
                .Sheets("Source").Cells(j, 12).Value
                .Cells(14, 11) = Workbooks("SD Invoice Automation") _
                .Sheets("Source").Cells(j, 11).Value
            End With
        
            i = i + 1
            j = j + 1
        Next scnum
        
        
        .Range("H1:H" & last).AdvancedFilter Action:= _
        xlFilterInPlace, CriteriaRange:=inv.Sheets(1).Range("K12")
        
        For Each lcat In .Range("D2:D" & last)
            i = 1
            inv.Sheets(i).Cells(k, 2) = .Cells(l, 4).Value
            inv.Sheets(i).Cells(k + 1, 2).EntireRow.Insert
            k = k + 2
            l = l + 1
        Next lcat
    End With
    
    
End Sub

 

[tweedle]

I'm not positive, but it seems your criteria should be
CriteriaRange:=inv.Sheets(1).Range("K11:K12")
K11 being either a field-header of the data or blank if K12 is a derived logical formula.