Just to add to Bob's reply. You'll want to use buttons from the Forms
toolbar--not commandbuttons from the Control toolbox.

And then:

Option Explicit
Sub Giselle()
Dim R1 as String
dim R2 as string
dim R3 as string

R1 = ""
R2 = ""
R3 = ""

Select Case lcase(Application.Caller)
Case "button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O:P"
Case "button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "U:V" '<--changed
End Select

if r1 = "" then
beep
msgbox "Design error. See Giselle!
exit sub
end if

call Common(r1,r2,r3)

End Sub

Sub Common(R1 as String, R2 as String,R3 as String)
Set FirstRangeToWorkOn = Range(R1)
Set StringToWorkOn = Range(R2)
Set SecondRangeToWorkOn = Range(R3)
.....

End Sub

I think I'd be a little specific with what worksheet, too:

Sub Common(R1 as String, R2 as String,R3 as String)

with worksheets("Sheet99") 'or Activesheet ????
Set FirstRangeToWorkOn = .Range(R1)
Set StringToWorkOn = .Range(R2)
Set SecondRangeToWorkOn = .Range(R3)
end with

.....

End Sub

And all of it goes into a general module--remember use the button from the Forms
toolbar.

(if you select the button, you can type the name you like in the
namebox--remember to hit enter when you're done changing it.)

Giselle wrote:

Just a few followup clarifications:

The case I gave in the original post was slightly simplified. There are
actually 3 distinct ranges needed each time the button is clicked.
Something like:

Sub Giselle()
Select Case Application.Caller
Case "Button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O:P"
Common
Case "Button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "UV"
Common
etc..

So... would the common sub look something like this?

Sub Common(R1 as String, R2 as String,R3 as String)
Set FirstRangeToWorkOn = Range(R1)
Set StringToWorkOn = Range(R2)
Set SecondRangeToWorkOn = Range(R3)
.... etc.

Thanks! I'm really new at this. I'm assuming all of the above goes in a
Module. Giselle

"Bob Umlas" wrote in message
...
Have each button call the exact same routine, say "Giselle"
Each button has a name, like Button 1, Button 2, etc. You can see which
button was clicked by Application.Caller:
Sub Giselle()
Select Case Application.Caller
Case "Button 1"
TheRange = "A1:A5"
Common
Case "Button 2"
TheRange = "G4:G88"
Common
Case "Button 3"
TheRange = "N3:Z3"
Common
'....etc
End Select
End Giselle
Sub Common(Rg as String)
Set RangeToWorkOn = Range(Rg)
'...now work with RangeToWorkOn
End Sub

instead of putting 5 buttons on each sheet, why not use a new commandbar
with 5 controls (each a commandbutton) - that way it'd always be visible,
no
matter which sheet you're on.

Bob Umlas
Excel MVP
"Giselle" wrote in message
...
Greetings

I'm fairly new at XL programming and I need a bit of help.

I have a worksheet that contains, among other things, 5 command Buttons.

Part 1: The 40+ lines of code in each button is identical, except for
some range references. ( eg cmdA, cmdB ...etc refer to
Range(D5:E25),
Range(J5:K25) ...etc). Can someone suggest how I can only enter the
code
once to save space and have each button run the code with its own
appropriate ranges.

Part 2: There are eventually going to be 24 worksheets like the one
above, each having these same 5 buttons. On each worksheet, cmdA does
the
exactly same thing. (same for cmdB, etc). Ideally, I'd like the code to
be
usable by ALL buttons (24x5=120) in the workbook. I'm not sure how to
proceed.

Thank you very much for any help
Giselle





--

Dave Peterson