Previous Topic Next Topic

Need to create linked sheets

Closed
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020 - Aug 18, 2019 at 03:27 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - 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

2 replies

Answer 1 / 2
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Aug 20, 2019 at 12:06 PM
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
1
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Updated on Aug 20, 2019 at 12:34 PM
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
0
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Aug 20, 2019 at 02:40 PM
If there is another possible solution Please help me with this
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Aug 22, 2019 at 11:59 AM
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
0
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Aug 22, 2019 at 12:06 PM
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
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Aug 26, 2019 at 12:02 PM
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
0
Answer 2 / 2
luigidelmonte Posts 18 Registration date Sunday August 18, 2019 Status Member Last seen January 24, 2020
Aug 18, 2019 at 03:29 PM
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
0