Results 1 to 4 of 4

Thread: Bizzare Request (If it's possible)

  1. #1

    Bizzare Request (If it's possible)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    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

  3. #3
    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!

  4. #4
    Quote Originally Posted by keanoppy View Post
    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.

    Quote Originally Posted by keanoppy View Post
    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.

    Quote Originally Posted by keanoppy View Post
    Code:
    Target.EntireRow.Copy Worksheets(shName).Cells(RowNum, "A")
    Cells(RowNum, "A") <--- what does A stands for?
    Column A.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •