Using a cell selection as a variable in excel vba

[BKirkham]

Hi,

I have 3 sheets. Sheet 1 (RAG Open) has 140 locations in column B. I want the user to be able to click on any location in column B and for the vba to take that choice and paste it into A3 on sheet 2 (formulas), whilst taking the user to sheet 3(office view), which is now populated with a more detailed breakdown of their choice. The tables and formulas in the 3rd sheet are reading the results based on the selection now pasted into A3 on sheet 2.

I have managed to get this to work, but my result is very long winded and I wondered if someone could help me shorten it please?

NB.This VBA is written on the 'RAG open' sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("b4")) Is Nothing Then 'Call MyCopyMacro1
If Not Intersect(Target, Range("b5")) Is Nothing Then Call MyCopyMacro2
If Not Intersect(Target, Range("b6")) Is Nothing Then Call MyCopyMacro3
etc...

End If
End Sub


Sub MyCopyMacro1()


Dim ws, ws1, ws2 As Worksheet
Dim CurrValue As Long
CurrValue = ActiveCell.Value
Set ws = Sheets("RAG open")
Set ws1 = Sheets("Formulas")
Set ws2 = Sheets("Office View")
ws.CurrValue.Copy
ws1.Cells(4, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
ws2.Activate


End Sub
Sub MyCopyMacro2()


Dim ws, ws1, ws2 As Worksheet
Set ws = Sheets("RAG open")
Set ws1 = Sheets("Formulas")
Set ws2 = Sheets("Office View")
ws.Cells(5, 2).Copy
ws1.Cells(4, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
ws2.Activate


End Sub
etc...

Thank you for your time in reading through this.

 

[steve the fish]

Hi. I think id use a double click event for this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Rng as Range
Set Rng = Intersect(Target, Range("B4:B150"))

If Not Rng Is Nothing Then
    Sheets("Formulas").Range("A3") = Rng.Value
    Application.Goto Sheets("Office View").Range("A1"), True
End If
    
End Sub

 

[BKirkham]

Sorry to take so long to reply. Only just found time to try out your suggestion, and it works brilliantly. So much more succinct than my attempt.

Thank you Steve the Fish