Hello Everyone!

I'm new to this forum and just hope I'm doing things correctly!
I have been using the MrExcel forums (which have been amazingly helpful! shoutout to Dangler for the coding) but I wanted to expand.

The current issue I am faced with is how to populate a row, depending on the condition typed in the end cell, to the corresponding sheet tab.
This would be a "global button" on my cover sheet which would run through the itemized tabs and move the rows by the end cell designator (see below)

For example:
Information is entered in starting in cells B6-N6 and down. In cell "O2" I would like to type a letter (C, L, H, O) and depending on the letter typed in "O2" the row information will be moved to its corresponding sheet. The name of the sheets we have setup are CNC Ticket, Lumber Ticket, Hardware Ticket, Optimization Ticket

I am using a excel 2016
All help is appreciated!
ML

Here is the code that I have changed a little just trying to troubleshoot.
I am getting the "Run-time error '9': Subscript out of range" debug error on the Underlined code

a = 2
ws = ""
rows.count = 1048576
xLup = -4162

Option Explicit

Sub CommandButton1_Click()
Dim a As Integer
Dim ws As String
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "CNC Ticket" Then
If sh.Name <> "Lumber Ticket" Then
If sh.Name <> "Optimization Ticket" Then
If sh.Name <> "Hardware Ticket" Then
With sh
For a = 2 To .Cells(.Rows.Count, 2).End(xlUp).Row
Select Case .Cells(a, 15).Value
Case Is = "C"
ws = "CNC Ticket"
Case Is = "L"
ws = "Lumber Ticket"
Case Is = "O"
ws = "Optimization Ticket"
Case Is = "H"
ws = "Hardware Ticket"
End Select
.Range("B" & a & ":N" & a).Copy Worksheets(ws).Range("B" & Rows.Count).End(xlUp).Offset(1)[/I]
Next a
End With
End If
End If
End If
End If
Next sh
End Sub



Here is an example item sheet. We have varying item sheets 730,738,746,900,903,950 depending on the job.
Cab# is in cell B5



900 Serving Line WS
JOB # 16600 CUSTOMER Matt Test
DATE LOCATION St.Joseph MO
DESCRIPTION TOTAL CABINETS 1
Cab # Qty Material Thick Width Length Part Description Notes Edge1 Edge2 Edge3 Edge4
900 1 MDF 0.5 12 12 Panel sand 1 2 3 4 pid C
900 1 PB 0.5 12 12 Sub sand 1 2 3 4 pid C
900 1 Lam 0.5 12 12 top sand 1 2 3 4 pid L
900 1 Lam 0.5 12 12 bottom sand 1 2 3 4 pid L
900 1 Lam 0.5 12 12 front sand 1 2 3 4 pid O
900 1 Plastic 0.5 12 12 Trim sand pid O
900 2 Plastic 0.5 12 12 Trim sand pid O
900 3 Plastic 0.5 12 12 Trim sand pid S
900 1 Corian 0.5 12 12 top sand 1 2 3 4 pid S
900 1 Corian 0.5 12 12 top sand 1 2 3 4 pid H
900 1 Corian 0.5 12 12 top sand 1 2 3 4 pid H

Here is an example "ticket" sheet
On this sheet and the other "ticket" sheets, CAB# is in A6

Lumber TICKET
JOB # 16600 CUSTOMER Matt Test
DATE LOCATION St.Joseph MO
DESCRIPTION
Cab # Qty Material Thick Width Length Part Description Notes Edge Edge2 Edge3 Edge4





Im just trying to break everything down for you.

Thank you,
Matthew L.