Too many macros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barney - 25 Feb 2008 11:59 GMT

In this spreadsheet for my golfing group, I have 45 macros and 45 buttons to
run them. One macro & button per golfer. Is there a way to do the same thing
with one macro and one button where I would merely place the cursor in the
proper name cell and then click the button?

E F G H I
Name Temp Points Rounded New
Quota Scored Diff 1/2 Quota
Bob 24 31 3 27
Fred 21 23 1 22
Hank 22 18 -2 20

A typical macro...............one of 45....... this one for Bob.....

Sub MoveStuff1()
Range("I7").Select
Selection.Copy
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G7").Select
Selection.ClearContents
End Sub

Thanks in advance,

Barney

Reply to this Message

Don Guillett - 25 Feb 2008 13:45 GMT

sub doall()
For i=2 to cells(rows.count,"e").end(xlup).row
cells(i,"f").value=cells(i,"I").value
cells(i,"g").clearcontents
next i
end sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> In this spreadsheet for my golfing group, I have 45 macros and 45 buttons
> to run them. One macro & button per golfer. Is there a way to do the same
[quoted text clipped - 24 lines]
>
> Barney

Reply to this Message

Don Guillett - 25 Feb 2008 14:02 GMT

My post does all at once with one mouse click. If you want to click for JUST
one then use a doubleclick event which MUST go in the sheet module for the
sheet where your data is. Then double click on the name

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
if target.column<> 5 then exit sub ' column E
i=target.row
cells(i,"f").value=cells(i,"I").value
cells(i,"g").clearcontents
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> sub doall()
> For i=2 to cells(rows.count,"e").end(xlup).row
[quoted text clipped - 31 lines]
>>
>> Barney

Reply to this Message

Bernard Liengme - 25 Feb 2008 13:49 GMT

Try this:
Sub Golfer()
Set mc = Selection
myName = Selection.Value
myColumn = Mid(mc.Address(columnabsolute:=False), 1, 1)

If Len(myName) = 0 Or myColumn <> "E" Then
MsgBox "Please select a name"
Exit Sub
End If

mycell = mc.Address
myreply = MsgBox("Do you want to fix " & myName, vbYesNo)
If myreply = vbYes Then
mySource = ActiveCell.Offset(ColumnOffset:=4)
ActiveCell.Offset(ColumnOffset:=1).Value = mySource
ActiveCell.Offset(ColumnOffset:=2).Clear
End If

End Sub

best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> In this spreadsheet for my golfing group, I have 45 macros and 45 buttons
> to run them. One macro & button per golfer. Is there a way to do the same
[quoted text clipped - 24 lines]
>
> Barney

Reply to this Message

Barney - 01 Mar 2008 12:47 GMT

Thanks to both Don and Bernard. Both solutions worked great. I ended up
using Bernard's because there was no use for the 'do all' capability of
Don's.

Barney

Reply to this Message

Bernard Liengme - 01 Mar 2008 15:05 GMT

Glad to have helped

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> Thanks to both Don and Bernard. Both solutions worked great. I ended up
> using Bernard's because there was no use for the 'do all' capability of
> Don's.
>
> Barney

Reply to this Message

Rate this thread: