Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable target address
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable target address
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Target.Address Question | Excel Programming | |||
Can you use a Range $N$xx:$Q$xx in Target.Address? | Excel Programming | |||
Using Target.Address in Excel 98 | Excel Discussion (Misc queries) | |||
Target.Address syntax | Excel Worksheet Functions | |||
target address | Excel Programming |