Bizzare Request (If it's possible)

K

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
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
 
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!
 
keanoppy said:
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?
Click to expand...

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.

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

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

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

Column A.
 
You must log in or register to reply here.
Back
Top