Forum Discussion
gbavikati
Jan 10, 2025Copper Contributor
VBA requirement
Hello, I have requirement in VBA.
When I click on Sheet1 CellA3 then I should redirect to Sheet2 and display the Sheet1 clicked cell value and its adjacent cell value as well CellB3.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim searchValue As Variant
Dim foundCell As Range
Dim targetSheet As Worksheet
If Target.Worksheet.Name = "Sheet2" Then
If Not Intersect(Target, Target.Worksheet.Columns("A")) Is Nothing Then
searchValue = Target.Value
Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
Set foundCell = targetSheet.Columns("A").Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
If Not foundCell Is Nothing Then
Target.Worksheet.Range("B1").Value = foundCell.Offset(0, 1).Value
Else
Target.Worksheet.Range("B1").Value = "No Match Found"
End If
End If
End If
- AdeelazizBrass Contributor
Replace your VBA code with this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim targetSheet As Worksheet
Dim clickedCellValue As Variant
Dim adjacentCellValue As Variant' Check if the clicked cell is A3
If Not Intersect(Target, Me.Range("A3")) Is Nothing Then
' Set the target sheet to Sheet2
Set targetSheet = ThisWorkbook.Worksheets("Sheet2")
' Get the clicked cell value and its adjacent cell value
clickedCellValue = Target.Value
adjacentCellValue = Target.Offset(0, 1).Value
' Redirect to Sheet2
targetSheet.Activate
' Display the values in Sheet2
targetSheet.Range("A1").Value = clickedCellValue
targetSheet.Range("B1").Value = adjacentCellValue
End If
End Sub