Extracting certain info from one sheet and pasting it to other sheets

[crazedmonkey]

Hi All,

I hope someone can help! I've searched around for what I need and can find different variations of what I am trying to do but not actually get what I specifically need to do!

I have a spreadsheet with 5 sheets which are:

Master
DAE
Damon
Rick
F4U

On the master sheet are 3 columns of info, column B is the "Order Number", column C is the "Delivery Date" and column D is "Delivered By".
Column A is empty as that's where I have a button to run the macro.

What happens is that all the info gets entered onto the master sheet, but then I would like to seperate the info by "Delivered By" onto the corresponding sheets.

I currently have this macro:

Sub Macro1()

    Dim tfCol As Range, Cell As Object
    
    Set tfCol = Sheet1.Range("D2:D9999") 

    For Each Cell In tfCol
    
        If IsEmpty(Cell) Then
        Exit Sub
        End If
        
        If Cell.Value = "DAE" Then
        Cell.EntireRow.Copy
        Sheet2.Select  
        ActiveSheet.Range("A65536").End(xlUp).Select
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
        End If
        
        If Cell.Value = "Damon" Then
        Cell.EntireRow.Copy
        Sheet3.Select 
        ActiveSheet.Range("A65536").End(xlUp).Select
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
        End If
        
        If Cell.Value = "Rick" Then
        Cell.EntireRow.Copy
        Sheet4.Select 
        ActiveSheet.Range("A65536").End(xlUp).Select
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
        End If
        
        If Cell.Value = "F4U" Then
        Cell.EntireRow.Copy
        Sheet5.Select 
        ActiveSheet.Range("A65536").End(xlUp).Select
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
        End If
           
    Next

End Sub

It sort of works in some respects! It will find the Delivered By and copy from the master sheet and put the whole row into the corresponding sheets, however I have a couple of issues - It will only paste the info into 1 row - thus overriding each time and leaving me with just one row of information (the last row!).

I'm unable to find out how to paste it into the entire sheet and not just all of it on one row. I am very basic at this sort of thing so I apologise if I don't understand much of the technical bits that might be posted as a reply!

The master sheet would be something that would be constantly updated and the "Click here" button to run the macro would be pressed each time, so I would need it to run the whole process every time - either overriding the whole lot or starting from the bottom of the last pasted selection (or though I feel this is where it might get slightly complicated!!)

Thanks in advance for any information

Phil

 

[VoG]

Hi. Try like this

If Cell.Value = "DAE" Then
    Cell.EntireRow.Copy Destination:=Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)
End If

 

[crazedmonkey]

Hi VoG,

Thank you for the really fast reply, however I can't seem to get that to work.

I've put the code in but when I click the button nothing at all happens!
Not sure if it's something else I should be doing!?

 

[VoG]

I meant for you to replace

If Cell.Value = "DAE" Then
        Cell.EntireRow.Copy
        Sheet2.Select
        ActiveSheet.Range("A65536").End(xlUp).Select
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
        End If

with the code that I posted. Similarly for the other values of Cell.

 

[crazedmonkey]

That's exactly what I did! That's why I was a bit confused as to why nothing at all happened!

 

[VoG]

Are you sure? The screen wouldn't flicker as it would with your code. My code does the same as yours but without selecting anything.

 

[crazedmonkey]

Aha, it does work! Thanks for that, however the issue with only pasting on the different sheets is still there.

I have 20 lines that were delivered by DAE on the Master sheet, so this should mean that I have 20 lines on the DAE sheet, however it only shows 1 line, and its the last one done by DAE. It looks like it's pasting into row 2 on the DAE sheet and then writing over this with each new line, rather than creating a new line for each row on the master.

This is the same for each sheet, all showing just one row rather than the right amount of rows for each one.

 

[VoG]

Sorry, I missed that column A was unused. Try like this

If Cell.Value = "DAE" Then
    Cell.EntireRow.Copy Destination:=Sheet2.Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
End If

 

[crazedmonkey]

Strangely that makes it stop working completely!! I don't know why though.

Sorry to add to the confusion!!

 

[VoG]

Sorry, if the code before worked but overwrote I can't understand why the new code would do nothing