Related:
- Need to create linked sheets
- Excel vba create new sheet with name from cell - How-To - Excel
- How to create new sheets for each row in excel? ✓ - Forum - Excel
- How to create link in excel to open pdf - How-To - Excel
- Macro to create new sheet and copy data ✓ - Forum - Excel
- Creating a macro that will create a new sheet and copy data from - Forum - Excel
2 replies
TrowaD
- Posts
- 2656
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 17, 2020
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:
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
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
luigidelmonte
- Posts
- 18
- Registration date
- Sunday August 18, 2019
- Status
- Member
- Last seen
- January 24, 2020
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
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
- Posts
-
2656
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 17, 2020
4401. 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
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
-
2656
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 17, 2020
440So 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
-
2656
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- September 17, 2020
440http://ge.tt/9QiVVlx2