filtering inventory list to separate supplier order forms but code crashes if not all suppliers included

[keithmct]

I have a master price list for ordering stock and the following code works as long as I'm always ordering from all suppliers.
I filter out the all items which are being ordered when quantity required column is greater than zero (field 11 > 0)
Then I filter by supplier column (field 2) and copy any visible cells over to the order form for that supplier.
All is good until I don't order anything from 1 of the suppliers and code stalls. There are no visible cells to copy and paste therefore
it spits the dummy.
Here is my code:

Sub filterorderstoeachsupplier()
'
' filterorderstoeachsupplier Macro
'
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row

    Range("A2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$P$5000").AutoFilter Field:=11, Criteria1:=">0", Operator:=xlAnd
    ActiveSheet.Range("$A$2:$P$5000").AutoFilter Field:=2, Criteria1:="lumberyard"
    Range("A3:M" & LR).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("lumberyard").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Sheets("price list master copy").Select
    ActiveSheet.Range("$A$2:$P$5000").AutoFilter Field:=2, Criteria1:="paintshop"

    'in here need something like "If Nothing Visible Then Don't Do The Next Bit"
    'code crashes at this line

    Range("A3:M" & LR).SpecialCells(xlCellTypeVisible).Select 
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("paintshop").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    'End If
    Sheets("price list master copy").Select
    'Selection.AutoFilter
    
End Sub

I probably need an If/Then scenario?

 

[dmt32]

Hi,
try this update to your code

Sub filterorderstoeachsupplier()
    Dim lr As Long, FilterRange As Long
    Dim rng As Range
    Dim ExcludeHeaderRow As Boolean
    Dim wsDest As Worksheet
    
'set False to include header row
    ExcludeHeaderRow = True
    
'worksheet your data is beingcopied to
    Set wsDest = Worksheets("paintshop")
    
    Application.ScreenUpdating = False
    
'master worksheet
    With Worksheets("price list master copy")
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        With .Range("$A$1:$P$" & lr)
            .AutoFilter Field:=11, Criteria1:=">0", Operator:=xlAnd
            .AutoFilter Field:=2, Criteria1:="paintshop"
        End With
        Set rng = .AutoFilter.Range
    End With
    
'check there are some visible rows to copy
    FilterRange = rng.Columns(2).SpecialCells(xlCellTypeVisible).Count - 1
    
    If FilterRange > 0 Then
'exclude header row
    If ExcludeHeaderRow Then Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
        rng.SpecialCells(xlCellTypeVisible).Copy _
        wsDest.Cells(wsDest.Range("A" & wsDest.Rows.Count).End(xlUp).Row + 1, 1)
        MsgBox FilterRange & " Records Copied to " & wsDest.Name, 48, "Records Copied"
    Else
        MsgBox "No Matching Data Found", 48, "No Match"
    End If
    
    rng.AutoFilter
    Application.ScreenUpdating = True
End Sub

Dave

 

[keithmct]

Hi Dave, I tried your code but although in my test run I was ordering 5 items from the supplier "lumberyard" and no items from "paintshop", it didn't perform the operation that filters the lumberyard items out and copies them to the lumberyard order form. Nothing at all was copied and just the message box appeared.
I should mention that I might have 10 suppliers by the time I'm done so I will need this code to allow for checking additional suppliers' filtered data to see if there are any visible cells in each case.
Also each supplier has its own worksheet as an order form so I don't have just one destination sheet.

 

[dmt32]

Solution was is based on information provided – what you have not said is if it worked using the supplier filter in your published code?

Assuming it does & to make code flexible, you can try replacing hard coded values & point required filter criteria & worksheet destination to a range in your worksheet where you can enter the required details to filter.

e.g.
for destination sheet

Set wsDest = Worksheets(Range(“A1”).value)

For filter criteria

.AutoFilter Field:=2, Criteria1:=Range(“B1”).value

This is just an idea, you will need to adapt to meet your specific project need.

Dave

 

[keithmct]

OK, yes, it did work originally as the published code was a macro recording. Now I'm thinking (after a day of hashing my/your code around) it would just be easier to have a button for each supplier, filter the required orders that are both greater than zero and for that 1 supplier, copy that and paste to its own order worksheet in the first blank row in column A.
I tried this and I still have problems, so am giving up for today.
Thanks for your help and the new ideas.