locked
Move a line item from one worksheet into another worksheet in same workbook, but still keep line item on master sheet RRS feed

  • Question

  • I have an excel workbook that contains 4 worksheets with the following titles:

    1 - Master List

    2 - SU

    3 - MC

    4 - FR

    What I am trying to do is, when I insert a number into the field stated WO #, I want it to take the Group it is associated with and copy that line into the corresponding worksheet. I have inserted a sample of what the Master List looks like (sorry it's jumbled, it wouldn't let me insert an image). For the first line item it falls into the Group "SU", so when/and if I add a WO# to this line. I want it to take this line and copy it into the worksheet SU, but also keep the information in the Master List. Is this possible and if so could someone please help because I have been trying to figure it out with 2 days with only being able to create a headache for myself! Much appreciated.

    Here is a sample of what is on the Master List:

    Quote # Date Requested Company Description Group Date Quoted Price WO #
    EN1720 10/17/2013 Laser Industrial Cable SU 10/17/2013 $543.00
    Monday, October 21, 2013 4:51 PM

All replies

  • Hi Carballa85

       Below is some code that achieves what you want, but to get the line to copy when you fill in the WO# field, I think the only way would be to use the "Change" event.  However, this would also fire every time you made any other change to the worksheet.  While the sheet was small this would work OK, but as the amount of data on the sheet increased, so there would be a perceptible pause after every entry.  Alternatively you could create a keyboard shortcut to do it, or perhaps a button or an icon on the quick launch toolbar, and use that every time you made a change to the WO# column.

    However, a simpler way that might meet your needs would be to use the save and / or the close event.  The data would not be transferred immediately, but as soon as you saved or closed the workbook, it would transfer and be ready the next time you or anyone else opened the workbook.

    Sub CopyToGroupSheet()
    'Copy task to group sheet

    Dim lngMastLastRow As Long 'Bottom Master sheet row that contains data in Col H
    Dim lngCounter As Long  'Counter for loops
    Dim strTargetSheet As String 'Destination worksheet for data from Master sheet
    Dim lngTargetLastRow As Long 'Bottom used row in the target sheet.

    '   calculate last rwo in Master Sheet
        lngMastLastRow = Worksheets("Master List").Cells(Rows.Count, 8).End(xlUp).Row

    '   Clear out existing data in all but Master Sheet
        For lngCounter = 2 To 4
            Worksheets(lngCounter).Select
            Cells.Select
            Selection.ClearContents
            Worksheets("Master List").Range("A1:H1").Copy Destination:=Worksheets(lngCounter).Range("A1")
        Next lngCounter


    Worksheets("Master List").Select

    '   Step through the Master Sheet copying the data from each line and pasting in the appropriate sheet.
        For lngCounter = 2 To lngMastLastRow
            If Range("H" & lngCounter) <> "" Then
                strTargetSheet = Range("E" & lngCounter).Value
                lngTargetLastRow = ((Worksheets(strTargetSheet).Cells(Rows.Count, 1).End(xlUp).Row) + 1)
                Range(Cells(lngCounter, 1), Cells(lngCounter, 8)).Copy _ Destination:=Worksheets(strTargetSheet).Range("A" & lngTargetLastRow)
                
            End If
        Next lngCounter

    End Sub

    Clearly, there is no error trapping in the code above, but you should be able to get the basic idea that I have used.  The three worksheets other than the Master List are cleared each time the procedure runs, and all the data from the Master List is then copied to the relevant sheet. 

    Hope this gives you a usable idea

    Andy C

    Wednesday, October 23, 2013 6:57 PM
  • Do I just copy and paste this into the visual editor box? I have done this but it's still not working. I honestly don't know anything about macros. I took one class for them in college and that was about 6 years ago. I just want the line to move into the other worksheets when the # is added. Any simple way of doing this is fine.

    Thanks!

    Friday, October 25, 2013 1:03 PM
  • Hi Carballa85

    The code I posted assumes that you have the data you described in your workbook at rows 1 and 2 of the "Master List" worksheet, starting at column A, with the other three worksheets having just the header line in row 1.  The code I sent originally may have been corrupted by the word wrapping, so I have posted it again below to cater for that.  Copy it into the VB Editor in a new module in your worksheet.  Then enter some data into column H (under the WO# heading).  Then back to the VB Editor and press F5.  If you want to see the code stepping through the lines, press F8 repeatedly to see each line in action.

    If by "Any simple way of doing this" you mean without using VBA, I fear there is none.  Furthermore the code I posted is only to give you the basic idea of how to do it.  My comments earlier about using the Save or Close event still apply if you want to automate it, and they would require at least a basic understanding of how to use VBA.  I don't think that there is a way of simply recording a macro, since the code has to make decisions, based on the contents of column E (Group), on the appropriate sheet into which to paste the data.

    Sub CopyToGroupSheet()
    'Copy task to group sheet

    Dim lngMastLastRow As Long 'Bottom Master sheet row that contains data in Col H
    Dim lngCounter As Long  'Counter for loops
    Dim strTargetSheet As String 'Destination worksheet for data from Master sheet
    Dim lngTargetLastRow As Long 'Bottom used row in the target sheet.

    '   calculate last rwo in Master Sheet
        lngMastLastRow = Worksheets("Master List").Cells(Rows.Count, 8).End(xlUp).Row

    '   Clear out existing data in all but Master Sheet
        For lngCounter = 2 To 4
            Worksheets(lngCounter).Select
            Cells.Select
            Selection.ClearContents
            Worksheets("Master List").Range("A1:H1").Copy Destination:=Worksheets(lngCounter).Range("A1")
        Next lngCounter


    Worksheets("Master List").Select

    '   Step through the Master Sheet copying the data from each line and pasting in the appropriate sheet.
        For lngCounter = 2 To lngMastLastRow
            If Range("H" & lngCounter) <> "" Then
                strTargetSheet = Range("E" & lngCounter).Value
                lngTargetLastRow = ((Worksheets(strTargetSheet).Cells(Rows.Count, 1).End(xlUp).Row) + 1)
                Range(Cells(lngCounter, 1), Cells(lngCounter, 8)).Copy Destination:=Worksheets(strTargetSheet).Range("A" & lngTargetLastRow)
                
            End If
        Next lngCounter

    End Sub

    Hope this clarifies it a bit.

    Andy C

    Friday, October 25, 2013 2:38 PM
  • Hi again Carballa85

    Having thought a bit further on your problem, there might be a way of displaying the information that you want without using VBA.

    First, you could use the filter function on the Master List sheet to display only the rows that have something entered (i.e. non-blank) in the WO# column and then filtering for the Group initials that you want.

    Or, if you want separate sheets for each Group, you could use a formula in each of the cells (except the header row) in columns A to H.  In sheet FR, for example, the formula for cell A2 would be =IF('Master List'!$H2="",,IF('Master List'!$E2="FR",'Master List'!A2,"")) .  You would need to set up the cell A2 formula for each sheet with the appropriate Group letters, but thereafter you should be able to do an autofill copy using the fill handle.

    The problem with the formula idea is that you will have blank rows on each sheet where the Master List has rows that do not relate to the active sheet.

    Hope this helps

    Andy C

    Saturday, October 26, 2013 7:29 AM