Prev: Creating several TextBoxes (Shapes) with cell references as contents
Next: VBA Editor
From: joemeshuggah on 17 Dec 2009 12:57
i have two worksheets...one that has a list of names , and a detail sheet
that has data for each name.

i am looking to have a macro that would create a third sheet that summarizes
the detail in the data sheet for the name that is double clicked in the name
sheet without having to create a separate macro for each name. is this
possible?
From: Ryan H on 17 Dec 2009 14:23
Put this in the Names Worksheet Module. When ever you double click the name
it will find the data in Data worksheet and put it in summary worksheet. You
need to relabel your worksheets, Names, Data, & Summary. Let me know if you
need me to expand on this code. Hope this helps! If so, let me know, click
"YES" below.
Option Explicit

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

Dim rngSearchRange As Range
Dim rngFound As Range
Dim lngLastRow As Long

Cancel = True

With Sheets("Data")
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row

Set rngSearchRange = .Range("A1:A" & lngLastRow)

Set rngFound = rngSearchRange.Find(What:=Target.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True, _
SearchFormat:=False)
End With

If Not rngFound Is Nothing Then
With Sheets("Summary")
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(lngLastRow, "A").Value = Target.Value
.Cells(lngLastRow, "B").Value = rngFound.Offset(0, 1).Value
End With
MsgBox Target.Value & " has been added to the Summary sheet."
Else
MsgBox Target.Value & " was not found in Data Sheet.", vbInformation
End If

End Sub
--
Cheers,
Ryan


"joemeshuggah" wrote:

> i have two worksheets...one that has a list of names , and a detail sheet
> that has data for each name.
>
> i am looking to have a macro that would create a third sheet that summarizes
> the detail in the data sheet for the name that is double clicked in the name
> sheet without having to create a separate macro for each name. is this
> possible?
 | 
Pages: 1
Prev: Creating several TextBoxes (Shapes) with cell references as contents
Next: VBA Editor