Copy Rows to Different Tabs

[matthewrlong]

Hello Everyone!

I'm new to this forum and just hope I'm doing things correctly!

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 tab.

For example:
Row 2 thru Row 5 have information in cells A-N. In cell "O2" I would like to type a letter (C, L, H) 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.

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

 

[matthewrlong]

So here is the code that I have changed a little just trying to troubleshoot. A decision was made to add an "Optimization Ticket".
I am still getting the "Run-time error '9': Subscript out of range" debug error.

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

Code:
[CommandButton1] [Click]
_________________________________________________________

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)
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.
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

<tbody>
</tbody>

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

CNC 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

<tbody>
</tbody>

Im just trying to break everything down for you.

Thank you,
Matthew L.

 

[dangelor]

Try this...

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 = 6 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("A" & Rows.Count).End(xlUp).Offset(1)
                            Next a
                        End With
                    End If
                End If
            End If
        End If
    Next sh
End Sub

 

[matthewrlong]

That works BEAUTIFULLY!! thank you so much Dangelor!! major props to you.

I'm working on another button now and will definitely contact you if i need help.
I'm gonna work on teaching myself.

Thank you
ML

 

[dangelor]

Glad to know it worked!

 

[matthewrlong]

Sorry to bother you again Dangelor but I need some more help if you don't mind.
I'm not sure if this is the proper way to use the forum or not so I do apologize in advance!

I'm coming back to you because you are a very reliable source.

So here is the deal. I have now been asked to take the information on the "Optimization Ticket" and copy only certain information over.

I am using the following code.

Sub CommandButton2_Click()
Dim a As Integer
Dim ws As String

With Worksheets("Optimization Ticket")
For a = 6 To .Cells(.Rows.Count, 2).End(xlUp).Row
Select Case .Cells(a, 16).Value
Case Is = ""
ws = "Optimization Ticket"
End Select
.Range("B" & a & ":L" & a).Copy Worksheets("Process Ticket").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next a
End With
End Sub

Now it works fine except instead of grabbing the value from cell "C6" (which is our Quantity) it wants to grab the formula.
Is there any way to grab just numeric value in cell "C6".

Thank you
ML

 

[dangelor]

This may work...

Sub CommandButton2_Click()
    Dim a                As Integer
    Dim ws               As String
    Dim vRow             As Variant

    With Worksheets("Optimization Ticket")
        For a = 6 To .Cells(.Rows.Count, 2).End(xlUp).Row
            Select Case .Cells(a, 16).Value
                Case Is = ""
                    ws = "Optimization Ticket"
            End Select
            vRow = .Range("B" & a & ":L" & a)
            Worksheets("Process Ticket").Range("A" & Rows.Count).End(xlUp).Offset(1) _
                    .Resize(UBound(vRow, 1), UBound(vRow, 2)) = vRow
        Next a
    End With
End Sub

 

[matthewrlong]

Works!!

Thank you very much!

ML