Need to create linked sheets

Posts
9
Registration date
Sunday August 18, 2019
Status
Member
Last seen
November 23, 2019
- - Latest reply: TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
- Aug 26, 2019 at 12:04 PM
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
See more 

2 replies

Best answer
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
379
1
Thank you
Hi Luigi,

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.

Best regards,
Trowa

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5701 users have said thank you to us this month

luigidelmonte
Posts
9
Registration date
Sunday August 18, 2019
Status
Member
Last seen
November 23, 2019
-
If there is another possible solution Please help me with this
TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
379 -
Hi Luigi,

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?

Best regards,
Trowa
luigidelmonte
Posts
9
Registration date
Sunday August 18, 2019
Status
Member
Last seen
November 23, 2019
-
also the data just goes until column 5

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
TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
379 -
Hi Luigi,

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
TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
379 -
Can you replicate the issue in this sample file:
http://ge.tt/9QiVVlx2
Respond to TrowaD
Posts
9
Registration date
Sunday August 18, 2019
Status
Member
Last seen
November 23, 2019
0
Thank you
SOURCE SHEET


THIS IS THE SEPERATE SHEET THAT I NEED.


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
Respond to luigidelmonte