Copy Rows to Different Tabs

matthewrlong

New Member
Joined
Jan 18, 2017
Messages
11
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

Board Regular
Joined
May 6, 2005
Messages
138
Possibly...
Code:
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

New Member
Joined
Jan 18, 2017
Messages
11
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)

abcdefghijklmno
1JobCabinetQTYMatThWLPart DesNoteEdgeE2E3E4PidWS
2166001001mdf.751212PLATESandWht2mm3mm4mm100-PlateC

<tbody>
</tbody>








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


abcdefghijklmn
1
2
3
4
5CabinetQTYMatThWLPart DesNoteEdgeE2E3E4PID
61001mdf.751212PlateSandWht2mm3mm4mm100-Plate
7

<tbody>
</tbody>














Again your help is greatly appreciated!
ML
 

dangelor

Board Regular
Joined
May 6, 2005
Messages
138
Try this...
Code:
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

New Member
Joined
Jan 18, 2017
Messages
11
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

Board Regular
Joined
May 6, 2005
Messages
138
Is this what you wanted?
Code:
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

New Member
Joined
Jan 18, 2017
Messages
11
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

Board Regular
Joined
May 6, 2005
Messages
138
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.
 

dangelor

Board Regular
Joined
May 6, 2005
Messages
138
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").
 

Forum statistics

Threads
1,081,834
Messages
5,361,594
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top