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

crazedmonkey

New Member
Joined
Mar 8, 2011
Messages
7
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:

Code:
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 :)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hi. Try like this

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

crazedmonkey

New Member
Joined
Mar 8, 2011
Messages
7
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

Legend
Joined
Jun 19, 2002
Messages
63,650
I meant for you to replace

Code:
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

New Member
Joined
Mar 8, 2011
Messages
7

ADVERTISEMENT

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

New Member
Joined
Mar 8, 2011
Messages
7

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
Sorry, I missed that column A was unused. Try like this

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

crazedmonkey

New Member
Joined
Mar 8, 2011
Messages
7
Strangely that makes it stop working completely!! I don't know why though.

Sorry to add to the confusion!!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Sorry, if the code before worked but overwrote I can't understand why the new code would do nothing :confused:
 

Forum statistics

Threads
1,141,019
Messages
5,703,763
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top