Bizzare Request (If it's possible)

keanoppy

New member
Joined
May 30, 2011
Messages
5
Reaction score
0
Points
0
Dear Gurus,

First of all, I would like to say thank you for viewing my thread. I've covered some basics of MS excel, please do not mock me on why do I need this feature I'm about to ask, it is a requirement, please tell me if it is not possible >.<

Attached is a sample excel file, where you can see sheets named "Masterlist","s1","s2" & "s3". You can see at every sheet, the fields are the same which is "Id", "Name" & "Action".

Every row of data, the "Action" column would be most crucial for me. I do know that we can filter the column "Action" to see a list of names with selected "Action", but again, this is a requirement.

Here my question goes; is it possible, to automatically copy the whole row of data, to another sheet, when the "Action" drop down is changed?
i.e. when the user select Masterlist, the whole row of data is copy to Masterlist, and gets deleted at the current sheet?
From masterlist, if the user selects "Go to S1", the row from Masterlist gets copied at S1, and deleted at masterlist?

Thanks & regards.
 

Attachments

  • Sample.xlsx
    11.5 KB · Views: 20

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Put this is the ThisWorkbook code module

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim RowNum As Long
Dim shName As String

    If Target.Column = 3 Then
    
        shName = Target.Value
        If shName Like "Go to *" Then
        
            shName = Right$(Target.Value, Len(Target.Value) - 6)
        End If
        If shName <> Sh.Name Then
        
            RowNum = Application.CountA(Worksheets(shName).Columns(1)) + 1
            Target.EntireRow.Copy Worksheets(shName).Cells(RowNum, "A")
            Target.EntireRow.Delete
        End If
    End If
End Sub
 

keanoppy

New member
Joined
May 30, 2011
Messages
5
Reaction score
0
Points
0
That worked! Thanks a lot.
I have some question though.
What does this row do:

Code:
shName = Right$(Target.Value, Len(Target.Value) - 6)

Why -6?Also, what does Right$ do?

Code:
RowNum = Application.CountA(Worksheets(shName).Columns(1)) + 1
this code + 1 because of the title row right?

Code:
Target.EntireRow.Copy Worksheets(shName).Cells(RowNum, "A")

Cells(RowNum, "A") <--- what does A stands for?

Thanks a bunch!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I have some question though.
What does this row do:

Code:
shName = Right$(Target.Value, Len(Target.Value) - 6)
Why -6?Also, what does Right$ do?

Because your dropdowns say 'Go to Sh1' rather than just Sh1, so I have to strip off the left-most 6 character, 'Go to ', hence I take the len of the string -6, those right-most characters.

Code:
RowNum = Application.CountA(Worksheets(shName).Columns(1)) + 1
this code + 1 because of the title row right?

No, it is because you don't want to overwrite the last row, so you add 1 to get the next free.

Code:
Target.EntireRow.Copy Worksheets(shName).Cells(RowNum, "A")
Cells(RowNum, "A") <--- what does A stands for?

Column A.
 
Top