If "yes" in a column copy other cells to the next tab.
Closed
Zaba
-
May 23, 2016 at 07:39 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - May 24, 2016 at 02:33 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - May 24, 2016 at 02:33 AM
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!
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!
Related:
- Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names.
- Ideogram ai names - Guide
- My contacts list names - Guide
- Wow monk names - Guide
- Laptop keyboard symbol names - Guide
- How to count names in excel - Guide
1 response
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
May 24, 2016 at 02:33 AM
May 24, 2016 at 02:33 AM
Hello Zaba,
The following code may do the task for you:-
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.
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.