[Solved] VBA Not working

[Gredang]

I have a workbook with 2 worksheets; worksheet 1 was given a name as "Names" and worksheet 2 was given a name as "Data".

In worksheet 1, I have a list of data which consist of column A - Name, column B - number, column C - Tpass, column D - pic,etc. I have a total of 60 rows with these data. Each row in column A has a different name. Eg: Row A1 is Jasmine, Row A2 is Wicha, Row A3 is Won and so on until row 65. I wan to, when I click on row A1, Jasmine's name, it will appeared all the details sheet 2 (meaning to say, once i click on Jasmine, Jasmine will appear is C2 data tab and that it will show the rest of the details. And if I click on Wicha, Wicha's details will be shown in sheet 2. However I do not know how to put into macro formula. Pls check if my formula is right. Pls kindly assist me. Thanks alot!

 

[Lutfor Rahman Shimanto]

I have a workbook with 2 worksheets; worksheet 1 was given a name as "Names" and worksheet 2 was given a name as "Data".

In worksheet 1, I have a list of data which consist of column A - Name, column B - number, column C - Tpass, column D - pic,etc. I have a total of 60 rows with these data. Each row in column A has a different name. Eg: Row A1 is Jasmine, Row A2 is Wicha, Row A3 is Won and so on until row 65. I wan to, when I click on row A1, Jasmine's name, it will appeared all the details sheet 2 (meaning to say, once i click on Jasmine, Jasmine will appear is C2 data tab and that it will show the rest of the details. And if I click on Wicha, Wicha's details will be shown in sheet 2. However I do not know how to put into macro formula. Pls check if my formula is right. Pls kindly assist me. Thanks alot!

Hello Gredang

Thanks for posting your issues in ExcelDemy Forum. The requirement you mentioned can be implemented by using an event procedure. I have reviewed your Workbook and developed an event to achieve your goal.

Excel VBA Event Procedure:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim wsNames, wsData As Worksheet
    Dim rng As Range
    Dim selectedRowNo As Long
    
    Set wsNames = ThisWorkbook.Sheets("names")
    Set wsData = ThisWorkbook.Sheets("data")
    Set rng = wsNames.Range("A5:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    If Not Intersect(Target, rng) Is Nothing Then
        selectedRowNo = Target.Row
        'MsgBox "Row number: " & selectedRowNo, vbInformation, "Clicked Cell"
        
        wsData.Activate
        wsData.Range("A1").Select
        
        wsData.Range("C2").Value = wsNames.Range("A" & selectedRowNo).Value
        wsData.Range("C4").Value = wsNames.Range("B" & selectedRowNo).Value
        wsData.Range("C6").Value = wsNames.Range("E" & selectedRowNo).Value
        wsData.Range("C8").Value = wsNames.Range("F" & selectedRowNo).Value
        wsData.Range("C10").Value = wsNames.Range("G" & selectedRowNo).Value
        wsData.Range("C12").Value = wsNames.Range("H" & selectedRowNo).Value
        wsData.Range("C14").Value = wsNames.Range("I" & selectedRowNo).Value
        wsData.Range("C16").Value = wsNames.Range("J" & selectedRowNo).Value
        wsData.Range("C18").Value = wsNames.Range("K" & selectedRowNo).Value
        wsData.Range("C20").Value = wsNames.Range("L" & selectedRowNo).Value
        wsData.Range("C22").Value = wsNames.Range("M" & selectedRowNo).Value
        wsData.Range("C24").Value = wsNames.Range("N" & selectedRowNo).Value
        wsData.Range("C28").Value = wsNames.Range("O" & selectedRowNo).Value
        wsData.Range("C32").Value = wsNames.Range("P" & selectedRowNo).Value
        wsData.Range("C36").Value = wsNames.Range("Q" & selectedRowNo).Value
        wsData.Range("C34").Value = wsNames.Range("C" & selectedRowNo).Value
        wsData.Range("C38").Value = wsNames.Range("R" & selectedRowNo).Value
        wsData.Range("C42").Value = wsNames.Range("S" & selectedRowNo).Value
        wsData.Range("F6").Value = wsNames.Range("T" & selectedRowNo).Value
        wsData.Range("F8").Value = wsNames.Range("U" & selectedRowNo).Value
        wsData.Range("F10").Value = wsNames.Range("V" & selectedRowNo).Value
        wsData.Range("F12").Value = wsNames.Range("W" & selectedRowNo).Value
        wsData.Range("F14").Value = wsNames.Range("X" & selectedRowNo).Value
        wsData.Range("F16").Value = wsNames.Range("Y" & selectedRowNo).Value
        wsData.Range("F18").Value = wsNames.Range("Z" & selectedRowNo).Value
        wsData.Range("F22").Value = wsNames.Range("AA" & selectedRowNo).Value
        wsData.Range("F26").Value = wsNames.Range("AB" & selectedRowNo).Value
        wsData.Range("F29").Value = wsNames.Range("AC" & selectedRowNo).Value
        wsData.Range("F32").Value = wsNames.Range("AD" & selectedRowNo).Value
        wsData.Range("F36").Value = wsNames.Range("AE" & selectedRowNo).Value
        wsData.Range("F40").Value = wsNames.Range("AF" & selectedRowNo).Value
        wsData.Range("L6").Value = wsNames.Range("AG" & selectedRowNo).Value
        wsData.Range("I4").Value = wsNames.Range("D" & selectedRowNo).Value
        wsData.Range("K18").Value = wsNames.Range("AO" & selectedRowNo).Value
        wsData.Range("I20").Value = wsNames.Range("AP" & selectedRowNo).Value
        wsData.Range("M28").Value = wsNames.Range("AR" & selectedRowNo).Value
        wsData.Range("M29").Value = wsNames.Range("AS" & selectedRowNo).Value
        wsData.Range("M30").Value = wsNames.Range("AT" & selectedRowNo).Value
        wsData.Range("K32").Value = wsNames.Range("AU" & selectedRowNo).Value
        wsData.Range("M36").Value = wsNames.Range("AV" & selectedRowNo).Value
        wsData.Range("M38").Value = wsNames.Range("AW" & selectedRowNo).Value
        wsData.Range("K40").Value = wsNames.Range("AX" & selectedRowNo).Value
        wsData.Range("P28").Value = wsNames.Range("AY" & selectedRowNo).Value
        wsData.Range("P29").Value = wsNames.Range("AZ" & selectedRowNo).Value
        wsData.Range("P30").Value = wsNames.Range("BA" & selectedRowNo).Value
        wsData.Range("P32").Value = wsNames.Range("BB" & selectedRowNo).Value
        wsData.Range("P36").Value = wsNames.Range("BC" & selectedRowNo).Value
        wsData.Range("P38").Value = wsNames.Range("BD" & selectedRowNo).Value
'        wsData.Range("").Value = wsNames.Range("" & selectedRowNo).Value
'        wsData.Range("").Value = wsNames.Range("" & selectedRowNo).Value
        
    End If

End Sub

Steps:
Right-Click on the sheet name tab >> later click on View Code.

​

Insert the mentioned code in the sheet module >> click the Save icon.

​

Output:
As a result, we will see an output like the following.

OUTPUT VIDEO​

This idea will help you reach your goal. I have also attached the solution Workbook to help you understand better. Don't hesitate to get us again if you have any more questions.

Regards
Lutfor Rahman Shimanto