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

 

[crazedmonkey]

Sorry my mistake completely, I missed the (1, -1) bit on the end of the code.

That works fine now, but I do have a new issue! haha, sorry, bet you wish you never replied to me now!!
It will paste all the right rows in the right sheets however when I click to run the macro again it will paste the info all over again underneath the rows that have just been pasted, therefore having duplicate lines of information.
I will be updating the master sheet on a regular basis and it would be easier if it could overwrite the whole lot each time rather than pasting it again underneath (if that makes any sense at all!!)

 

[VoG]

Try adding this after your Dim statement (this assumes that you have headers in row 1)

Sheet2.UsedRange.Offset(1).ClearContents
Sheet3.UsedRange.Offset(1).ClearContents
Sheet4.UsedRange.Offset(1).ClearContents
Sheet5.UsedRange.Offset(1).ClearContents

 

[crazedmonkey]

Perfect!!

Thank you very much for all your help!!