How do I automatically copy multiple column ranges to another worksheet based on a specific reference?

[arkadd61]

I have a workbook I'm working on that has multiple worksheet in it. There are 2 worksheet specifically that I need assistance with. The "Renewals" worksheet and the "Scheduled" worksheet. I have attached a copy of the workbook in question to make it easier to understand. My goal is, if I enter in the word "Scheduled" into column "E" in the "Renewals" worksheet, I want it to automatically copy the corresponding data from the same row into the corresponding columns and rows in worksheet "Scheduled".

https://docs.google.com/file/d/0B8tD0ngyrbDRZDU2ckVIMFE1OGc/edit?usp=sharing

Thank you ahead of time for any assistance with this! I hope you all can help.

Semisi

 

[Boswell]

I did not look at your workbook.. but the below code should give you all the tools you need:

Private Sub Worksheet_Change(ByVal Target As Range)

    ' get scheduled worksheet
    Dim scheduled As Worksheet: Set scheduled = ThisWorkbook.Worksheets("Scheduled")
    
    ' only care about column E - ignore everything else
    If Intersect(Target, Me.Range("E:E")) Is Nothing Then Exit Sub
    
    Dim r As Integer: r = Target.Row
    If (UCase(Target.Value) = "SCHEDULED") Then
        ' if target value is is "scheduled" then
        ' move values from target's row in columns F - G to the corresponding position on scheduled sheet
        scheduled.Range("F" & r & ":" & "G" & r).Value = Me.Range("F" & r & ":" & "G" & r).Value
        
    Else
        ' if it is not scheduled (say you delete scheduled from a cell) then
        ' clear the values on the scheduled sheet
        scheduled.Range("F" & r & ":" & "G" & r).Value = ""
        
    End If
    
End Sub

In the above code, if you enter scheduled in column E then it will copy the values from the corresponding row in columns F through G over to the corresponding position on a sheet called 'Scheduled'. You can expand the column range by changing the column letters in Me.Range("F" & r & ":" & "G" & r).Value. Note this is a worksheet event - you have to put this code in the "Renewals" worksheet code module.

 

[arkadd61]

Thank you for your assistance. So I'm a novice when it comes to Excel. I thought you would just send me a formula that I would copy and paste into a cell. I'm not sure how to implement this code into the worksheet. Would you be willing to open my worksheet and set it up with an explination so I can see what you did?

 

[Boswell]

You can definitely use formulas to do this. The words 'automatically copy' made me think you were looking for a macro based solutions. You will want to use an IF Statement.. something similar to the following:
=IF(Renewals!$E3 = "Scheduled", Renewals!F2,"")