Move a whole row to a new worksheet row based on entry in one cell but not if input entry is in another cell

[RID]

Good afternoon, is there a formula that can be used to do the following, I have a worksheet for reviews that we have internal comments for our own use and responses to customer. I have a sneaky feeling that this will probably need some sort of VBA code but thought I would ask the question if can be done by formula first off. Currently we have that what ever is in column F on worksheet 1 is transferred into worksheet but as you can see when there is no entry in column F it still transfers the row because we have someting in column D.

If there is input in Column F 'Response to Customer' cells I would like to transfer the whole row onto a new worksheet which is the bottom screenshot, but if there is an input in Colum D 'Internal Comment' then the row is not transfered to the second worksheet.

Any pointers would be appreciated if I need to start looking at VBA where should I be looking at and is there specific tutorials that can get me going?

 

[mumps]

It looks like you have merged cells in your destination sheet. This will create problems for the macro that will be needed to do what you want. You should avoid using merged cells at all cost because they almost always create problems for macros. You will have to modify the destination sheet to remove all the merged cells. Once you have done that, please use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[RID]

Thanks mumps for your quick reply I have changed the destination sheet to remove merged cells, hope this works to upload.

MrExcel Forum Help.xlsm | Powered by Box

app.box.com

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Doc 1 sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make an entry in column F and press the RETURN key or TAB key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 6 Then Exit Sub
    Application.ScreenUpdating = False
    Dim desWS As Worksheet
    Set desWS = Sheets("Doc 2")
    If Target.Offset(, -2) = "" Then
        With desWS
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 3).Value = Range("A" & Target.Row).Resize(, 3).Value
            .Cells(.Rows.Count, "F").End(xlUp).Offset(1).Value = Range("F" & Target.Row).Value
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[RID]

Sorry I dont think I may have explained it very well am pretty useless at this kind of stuff. I've also removed the formulas which was in the original as not sure if that we doing something wrong to the code.

In Doc 1 if the first row that has an entry in Column F the whole row as in Columns B, C & F would be copied into the first available row which would be row 3 Columns B, C & F, then every other row which has Column F entry in it would then go into rows 10, 17, 24 and so on. But if the row entry in Doc 1 has an entry in column D then it is ignored the row stays in column D.

When I entered your code in the first-row entry from column F is copied to Doc 2 Column F but it looks like the rest of the row is copied into rows 25, 26, 27 & 28. I've uploaded the file again to show what I mean. Also if changes were made after I hit enter in column F it added a new line under the first row in column F.

Thanks for taking the time to look at this as it had me and my colleague stumped when looking at it first.

MrExcel Forum Help.xlsm | Powered by Box

app.box.com

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 6 Then Exit Sub
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, lRow As Long
    Set desWS = Sheets("Doc 2")
    lRow = desWS.Range("B" & Rows.Count).End(xlUp).Row
    If Target.Offset(, -2) = "" Then
        With desWS
            If lRow = 2 Then
                .Range("B" & lRow + 1).Resize(, 2).Value = Range("B" & Target.Row).Resize(, 2).Value
                .Range("F" & lRow + 1).Value = Range("F" & Target.Row).Value
            Else
                lRow = lRow + 7
                .Range("B" & lRow).Resize(, 2).Value = Range("B" & Target.Row).Resize(, 2).Value
                .Range("F" & lRow).Value = Range("F" & Target.Row).Value
            End If
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[RID]

You are a star that is what we call in Scotland 'pap on'

 

[mumps]

Glad it worked out.

 

[RID]

I have watched countless videos on you tube most of which only had tutorials where the cell had a condition like a yes or no or complete or incomplete so I was getting bamboozled. I really appreciate your help on this it has possibly given us such a huge help in tracking comments.

 

[RID]

Ive got one final question for you if you delete the information from Doc 1, the info stays the same in Doc 2 is there a way to add to the code that if you delete the info in Doc 1 it removes it from Doc 2 also?