If "yes" in a column copy other cells to the next tab. Closed

Question

Hi Guys,

I have a sheet with participant names and a lot of other information about their attendance. What I am trying to do is to copy their name and check-in/out dates if they need a hotel to another tab in the same excel sheet. So basically I have a column F with their names and column AE with yes/no values that indicate if they need a hotel accommodation. Column AG and column AH have the check-in and check-out dates.

What I need is that if AE has a yes, it will copy the values from F, AG and AH to the next tab. Is there a dynamic way to do this?  That if I add new values it will automatically also fill details on the next "Accommodation tab"?

Thank you in advance for any advice!

vcoolio · Answer

Hello Zaba,

The following code may do the task for you:-


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Columns("AE:AE")) Is Nothing Then Exit Sub

If Target.Value = "Yes" Then
        Cells(Target.Row, "F").Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        Range(Cells(Target.Row, "AG"), Cells(Target.Row, "AH")).Copy Sheet2.Range("B" & Rows.Count).End(3)(2)
        'Target.EntireRow.Delete
        End If
  
Sheet2.Columns.AutoFit
Sheet2.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

It is a Worksheet Change event which means that every time you enter "Yes" in any cell in Column AE, the three pieces of data that you need will be transferred to sheet2 immediately.

Following is the link to my test work book for you to peruse:-

https://www.dropbox.com/s/okfptq9zqr57tyf/Zaba%28Worksheet%20Change%20event%29.xlsm?dl=0

Enter "Yes" in any cell in Column AE and the relevant data will be transferred to sheet2.

In the above code, should you wish to delete the entire row of data once the relevant data has been transferred to sheet2, just remove the apostrophe from the begining of line 13 (in the green font).

I hope that this helps.

Cheerio,
vcoolio.

If "yes" in a column copy other cells to the next tab.

1 response

Similar discussions

Subscribe To Our Newsletter!