Macros for data transfer - one sheet to another

[Jackie90]

Macros for data transfer - one sheet to another
<hr style="color:#ebebeb; background-color:#ebebeb" size="1"> Hello All,

I hope everyone is having a good day, mine has been just dreadful because I have this Excel sheet I just can't figure out and my boss is giving me a hard time. If you could help me, I'd appreciate it a lot. I've been working on this for the past two days and truly have no idea where to start. It even took me 15 minutes to figure out how to post on here.


Right now I have one Master sheet called, 'Potentials'. I need this data to transfer into either the 'Engagement' or 'Out' sheet depending on what I write in the 'Response' column in the master sheet.

If I write, 'Out' in the 'Response' column of the master sheet, I want all the information in that row to be cut from the master sheet and transferred into the 'Out' Sheet.

If I write 'NDA' in the Response column of the master sheet, I want all the information in that row to be transferred to the 'Engagement' sheet and cut from the master sheet. However, this is the tricky part, once it is transferred to the 'Engagement' sheet, I need it to be cut and transferred to the 'Out' sheet if I write 'Out' in the Response column.

And I have one last request!!!

On all the sheets there is a column entitled, 'Last Date of Contact', I need all the sheets to always be sorted by date, the oldest date should always be on the top. (For example, 02/15/2011 should be above 03/20/2011 )


I truly will be grateful to anyone who can offer me assistance. I just hope this can be resolved, I really am on edge about it.




If you can help me, please let me know! I can email you the excel sheet that is giving me such grief! I really am terrible with these computers.

 

[John Davis]

Macros for data transfer - one sheet to another
<hr style="color:#ebebeb; background-color:#ebebeb" size="1"> Hello All,

I hope everyone is having a good day, mine has been just dreadful because I have this Excel sheet I just can't figure out and my boss is giving me a hard time. If you could help me, I'd appreciate it a lot. I've been working on this for the past two days and truly have no idea where to start. It even took me 15 minutes to figure out how to post on here.


Right now I have one Master sheet called, 'Potentials'. I need this data to transfer into either the 'Engagement' or 'Out' sheet depending on what I write in the 'Response' column in the master sheet.

If I write, 'Out' in the 'Response' column of the master sheet, I want all the information in that row to be cut from the master sheet and transferred into the 'Out' Sheet.

If I write 'NDA' in the Response column of the master sheet, I want all the information in that row to be transferred to the 'Engagement' sheet and cut from the master sheet. However, this is the tricky part, once it is transferred to the 'Engagement' sheet, I need it to be cut and transferred to the 'Out' sheet if I write 'Out' in the Response column.

And I have one last request!!!

On all the sheets there is a column entitled, 'Last Date of Contact', I need all the sheets to always be sorted by date, the oldest date should always be on the top. (For example, 02/15/2011 should be above 03/20/2011 )


I truly will be grateful to anyone who can offer me assistance. I just hope this can be resolved, I really am on edge about it.




If you can help me, please let me know! I can email you the excel sheet that is giving me such grief! I really am terrible with these computers.

There are probably much better ways, however, these may help or get you started?

Sub Jackie90()
'This assumes that Column D is the Response Column and Column E is the Last Date of Contact
'With this you have too Run the macro after you make entry.
Dim lrPo As Long
Dim lrEn As Long
Dim lrOu As Long
Dim i As Long

lrPo = Sheets("Potentials").Cells(Rows.Count, 1).End(3).Row
lrEn = Sheets("Engagement").Cells(Rows.Count, 1).End(3).Row
lrOu = Sheets("Out").Cells(Rows.Count, 1).End(3).Row

With Sheets("Potentials")

    For i = lrPo To 2 Step -1
    
        If Range("D" & i).Value = "OUT" Then
            Sheets("Out").Range("A2").EntireRow.Insert shift:=xlDown
            Range("D" & i).EntireRow.Cut Sheets("Out").Range("A2")
        End If

        If Range("D" & i).Value = "NDA" Then
            Sheets("Engagement").Range("A2").EntireRow.Insert shift:=xlDown
            Range("D" & i).EntireRow.Cut Sheets("Engagement").Range("A2")
        End If

    Next i
    
End With

With Sheets("Potentials")

    Rows("2:" & lrPo).Select
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
End With

With Sheets("Out")

    Rows("2:" & lrOu).Select
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End With

With Sheets("Engagement")

    Rows("2:" & lrEn).Select
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End With


End Sub
Sub Jackie90Two()
'This assumes that Column D is the Response Column and Column E is the Last Date of Contact
'With this you have too Run the macro after you make entry.
Dim lrEn As Long
Dim lrOu As Long
Dim i As Long

lrEn = Sheets("Engagement").Cells(Rows.Count, 1).End(3).Row
lrOu = Sheets("Out").Cells(Rows.Count, 1).End(3).Row

With Sheets("Engagement")

    For i = lrEn To 2 Step -1
    
        If Range("D" & i).Value = "OUT" Then
            Sheets("Out").Range("A2").EntireRow.Insert shift:=xlDown
            Range("D" & i).EntireRow.Cut Sheets("Out").Range("A2")
        End If

    Next i
    
End With

With Sheets("Out")

    Rows("2:" & lrOu).Select
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End With

With Sheets("Engagement")

    Rows("2:" & lrEn).Select
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End With


End Sub