Hello,
I have an excel sheet this is for a list of unavailable items however I need to create another sheet for 1 specific team. So my sheet is like the list of unavailable items for every brands and deliveries but we deliver in some hotels and this is where we update and put some items that are unavailable but we need the other sheet to show only the items for the hotel delivery. And this sheet is always changing items every time like say every 10 hours so it would be double work if we manually do it seperately for the hotel deliveries.
I have tried linking the entire row so that when we update and put the item in the "hotel" row it will show up but it's not sorted
(I will upload the snips)
System Configuration: Windows / Chrome 76.0.3809.100
The code below will take every new entry from column F of "86 sheet" and copies it to column A of "Hotel unavailable". Column A of "Hotel unavailable" is then sorted A>Z.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub
With Sheets("Hotel unavailable")
.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Target.Value
.Columns("A").Sort Key1:=.Columns("A"), Header:=xlYes
End With
End Sub
To use the code; right-click "86 sheet" sheets tab and select view code and paste the code in the big white field.
Hi Thank you for this! This is really helpful but now I'm facing another obstacle with this. Its working the way I want to but If I remove the item from the cell in my "86 sheet" it still stays in the "hotel unavailable" sheet. it has to be removed from there as well when I remove The item. also the data just goes until column 5, Whenever I copy more than that, its not appearing in my "hotel" sheet
1. Removing item from "Hotel unavailable" sheet when it is deleted from "86 sheet" sheet.
1a. You could run a different macro to delete the item from both sheets. You could assign a shortcut to the macro for easy acces.
1b. But I'm noticing you have the same value in column D. We could use that value to delete the item in the "Hotel unavailable" sheet. This won't require a different action, but when column D is empty the item won't get deleted from the "Hotel unavailable" sheet.
Code for solution 1b:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub
If Target.Value <> vbNullString Then
With Sheets("Hotel unavailable")
.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Target.Value
.Columns("A").Sort Key1:=.Columns("A"), Header:=xlYes
End With
Else
Sheets("Hotel unavailable").Columns("A").Find(Target.Offset(0, -2).Value).Delete
End If
End Sub
2. "also the data just goes until column 5"
Do you mean only 5 items will be copied to the "Hotel unavailable" sheet? This doesn't make any sense to me. Could you maybe give an example to help understand?
So what you are saying is that, looking at your example, only 5 out of 8 items were copied to the "Hotel unavailable" sheet? For an item to be copied it has to be reconfirmed (double-click cell and hit enter).
I'm still not sure if that is what you meant. But I can't figure out what else you could mean.
I wanna do this So when I place the items in the "hotel" row it will automatically show up in the "hotel unavailable" sheet but I need it to be sorted and not having spaces
6671 users have said thank you to us this month
- Posts
- 5
- Registration date
- Sunday August 18, 2019
- Status
- Member
- Last seen
- August 22, 2019
-- Posts
- 2525
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 19, 2019
365 -1. Removing item from "Hotel unavailable" sheet when it is deleted from "86 sheet" sheet.
1a. You could run a different macro to delete the item from both sheets. You could assign a shortcut to the macro for easy acces.
1b. But I'm noticing you have the same value in column D. We could use that value to delete the item in the "Hotel unavailable" sheet. This won't require a different action, but when column D is empty the item won't get deleted from the "Hotel unavailable" sheet.
Code for solution 1b:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub If Target.Value <> vbNullString Then With Sheets("Hotel unavailable") .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Target.Value .Columns("A").Sort Key1:=.Columns("A"), Header:=xlYes End With Else Sheets("Hotel unavailable").Columns("A").Find(Target.Offset(0, -2).Value).Delete End If End Sub2. "also the data just goes until column 5"
Do you mean only 5 items will be copied to the "Hotel unavailable" sheet? This doesn't make any sense to me. Could you maybe give an example to help understand?
Best regards,
Trowa
- Posts
- 5
- Registration date
- Sunday August 18, 2019
- Status
- Member
- Last seen
- August 22, 2019
-I mean I want all of the items for the "hotel" to be copied in the "hotel unavailable" sheet but when I was doing it, it just copies 5 items
- Posts
- 2525
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 19, 2019
365 -So what you are saying is that, looking at your example, only 5 out of 8 items were copied to the "Hotel unavailable" sheet? For an item to be copied it has to be reconfirmed (double-click cell and hit enter).
I'm still not sure if that is what you meant. But I can't figure out what else you could mean.
Best regards,
Trowa
- Posts
- 2525
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 19, 2019
365 -http://ge.tt/9QiVVlx2