Filling cells based on another cell content

[mattyn]

Hi all

A bit of a complicated on in my eyes.

Table is as shown

If a "N" was to be entered in cells G10:G21, I would like the corresponding row cell contents from column B to E to be copied into a new table starting at B35. In addition, if a further "N" is added in G10:G21, I want that corresponding cell contents to be copied into the next available row in the table starting at B35. I hope this makes sense! I have explained better I think in the image.

[/URL]******** async src="//embedr.flickr.com/assets/client-code.js" charset="utf-8">*********>[/IMG]

Many thanks in advance - this would really help me out!

Matt

 

[mrshl9898]

Like this?

Wasn't sure where the column ended, so guessed row 44

Private Sub Worksheet_Change(ByVal Target As Range)    Dim KeyCells As Range


    Set KeyCells = Range("G10:G21")


    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        If Target.Value = "N" Then
        Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Copy Cells(44, 2).End(xlUp).Offset(1, 0)
        End If
        
    End If
End Sub

 

[mattyn]

Blimey that was quick. Thank you so much.

Do I need to change anything in the script you posted (such as work sheet names etc?) and where do i paste it?

 

[mrshl9898]

It needs to go on the sheet where the data is, no changes needed

 

[mattyn]

OK Thanks.

I right click on the tab for that worksheet and "view code". On the top left window the correct worksheet is highlighted.

I copied and paste the code onto the code sheet at the very top - the "Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range" is all in red.

When I make changes to the data (i.e. change the Y to a N - I get an error - Compile Error - expected end of statement and the "Dim" above is highlighted!!

Apologies - but I don't really understand the code flow so have no idea where to start to fault diagnose!

Matt

 

[mrshl9898]

Found that error. Move the Dim to the next row (bad copy and paste, apologies)

 

[mattyn]

Thanks for that - awesome quick response.

However now, I do not get the error but the cells are not copying.

 

[mrshl9898]

Is "N" being entered? (Not "n")

Does this produce a message?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range


    Set KeyCells = Range("G10:G21")




    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        If Target.Value = "N" Then
        MsgBox "N entered"
        'Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Copy Cells(44, 2).End(xlUp).Offset(1, 0)
        End If
        
    End If


End Sub

 

[mattyn]

Hi again

thank you again for the swift reply - I am wondering if I have slightly mislead you - if I have - god I am sorry! I feel a bit silly!

Col G is populated by a formula (if and AND) and is based on other celles along that row - namely if a "N" appears anywhere along that row, Col G will then show a "N". It is acting as a kind of Master for that row. I don't get the message when the row criteria means Col G is a "N".

When I overtype the formula and type in "N" into Col G, I get the message "N entered" However nothing copies to the row 35.

Within the new code, this row is now in green:

'Range(Cells(Target.Row, 2), Cells(Target.Row, 3)).Copy Cells(44, 2).End(xlUp).Offset(1, 0)

Please don't bust a gut trying to fix this tonight - I need my sleep now as I am working tomorrow - just whenever you get chance.

Thank you so much again!

 

[mrshl9898]

That explains it. The macro I made was waiting for N to be entered when the cell was selected. So it looks like that will be no use here.

FYI - The line is green because I changed it to a comment, removing the ' will make it code again.

I'm sure someone will help out here.