Copy and Paste rows to multiple worksheets based on a cell value.

Try this:

Sub x()

Dim r As Range

For Each r In Sheets(1).Range("A2", Sheets(1).Range("A" & Rows.Count).End(xlUp))
    Select Case r.Offset(, 10)
        Case 1 To 14
            r.Resize(, 21).Copy Sheets(CStr(r.Offset(, 10) & "-gallon")).Range("A" & Rows.Count).End(xlUp)(2)
        Case Else
            r.Resize(, 21).Copy Sheets("14-gallon").Range("A" & Rows.Count).End(xlUp)(2)
    End Select
Next r

End Sub

Remove the space in 13 sheet name.

Awesome thanks so much. That was a lot less code than I thought it would take.

Thanks for your help and time.