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

 

[dangelor]

Possibly...

Sub not_tested()
    Dim a As Integer
    Dim ws As Worksheet
    
    With Sheet1
        For a = 2 To 5
            Select Case Cells(a, 15)
                Case Is = "C"
                ws = Worksheets("CNC Ticket")
                Case Is = "L"
                ws = Worksheets("Lumber Ticket")
                Case Is = "H"
                ws = Worksheets("Hardware Ticket")
            End Select
            .Range(.Cells(a, 1), Cells(a, 14)).Copy ws.Cells(.UsedRange.Rows.Count + 1, 1)
        Next a
    End With
End Sub

 

[matthewrlong]

I'm not sure I have inserted the code correctly. I just simply copied pasted and changed SUB to Private Sub Populate_Click()
I hope all of this isn't confusing as I'm a basic user of Excel and a novice to forum use.

I'll try and build an example spreadsheet below since I can upload the file itself

This table represents "Sheet 1". The amount of rows my vary from 1 to 100 (just depends on the job)
I have a "Populate" button put in place to perform the VBA action (if that makes sense)

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

1

Job

Cabinet

QTY

Mat

Th

W

L

Part Des

Note

Edge

E2

E3

E4

Pid

WS

2

16600

100

1

mdf

.75

12

12

PLATE

Sand

Wht

2mm

3mm

4mm

100-Plate

C

<tbody>
</tbody>

This table represents "CNC Ticket". The information would transfer from Sheet 1 @ B2-N2 and land in Sheet "CNC Ticket" @ B6-N6.

a

b

c

d

e

f

g

h

i

j

k

l

m

n

1

2

3

4

5

Cabinet

QTY

Mat

Th

W

L

Part Des

Note

Edge

E2

E3

E4

PID

6

100

1

mdf

.75

12

12

Plate

Sand

Wht

2mm

3mm

4mm

100-Plate

7

<tbody>
</tbody>

Again your help is greatly appreciated!
ML

 

[dangelor]

Try this...

Option Explicit

Sub not_tested()
    Dim a As Integer
    Dim ws As String
    
    With Worksheets("Sheet 1")
        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 = "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 Sub

 

[matthewrlong]

Dangelor,

Who ever you are............. YOU ARE AWESOME!

Sorry for the delay in response. I often wish there were 3 of me!


I do have one more question for you. If you are able to get around to it I would appreciate it..

I know this is set up to be used on a per sheet basis. Is there a way to make a workbook option for this.

We have many different items which get there own sheet tabs based on the inventory number.
So lets say out inventory is 900,901,902,903,904,905. Each of those numbers gets a tab in our job workbook depending on which ones we use.

Example Job:
A request for items 900, 901, & 903 from job #16000 comes in. We would only need to create sheet tabs for those specific items.
How would you make a general or "global" VBA button?

Thank you
ML

 

[dangelor]

Is this what you wanted?

Sub again_not_tested()
    'this should cycle through all worksheets in a workbook
    
    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 <> "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 = "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
    Next sh
End Sub

 

[matthewrlong]

So I am just now finally able to get back around to this project. My company reallocated me to another project for the last month.

When I run this a "Run-time error 9, subscript out of range" comes up. When I debug it highlights the following line

.Range("B" & a & ":N" & a).Copy Worksheets(ws).Range("B" & Rows.Count).End(xlUp).Offset(1)

Thanks again for your help. If I could upload the workbook I would.

 

[dangelor]

Well, there are two variables in that line of code and either or both could be the cause of the error.

Run the code again and when the error stops it, hover your mouse over the "a" and the "ws" and let me know the value of each.

 

[matthewrlong]

a = 2
ws = ""

 

[dangelor]

I would guess that one of the rows in one of the worksheets doesn't have a value of "C", "L" or "H" in column 15 ("O").