Re-using same macro applied to different Sheets in same Workbook

[jerrynesmith]

I have three sets of tabs (sheets) in the same workbook. Each of these sets of three sheets work precisely the same way, except they have different data sources. I have a macro the performs data manipulations for one set of sheets. I want to use that same macro against each of the other two sets of sheets. I do not want to copy the macro two times then "replace" the sheet names. That will work, but then I must maintain three macros. Example: I want to run the macro with "IRB" as the sheet to be selected, rather than "UserMgt":

Sheets("UserMgt").Select

Is there a way to change "UserMgt" to a variable that can be changed each time the macro is called?

[ranman256]

use ACTIVESHEET instead of hardcoded names like usermgt.
(and dont use activesheet.select, because you are already there)

[jerrynesmith]

I reckon I was not clear about my goal. I am not "already there". The macro is moving and manipulating data from one sheet to another (three sheets involved). So I kickoff the macro with a macro button. I want to tell the macro when I click the "UserMgt Update" button that it should use the three sheets beginning with "UserMgt". When I click the "IRB Update" button, the macro should use the three sheets beginning with "IRB".

[ranman256]

Then always select the workbook you want to be IN:

sheets("UserMgt").select
'do stuff here

sheets("IRB").select
sheets("UserMgt").range("A1")=true

'do stuff here

[jerrynesmith]

That is what I do now (sheets().select). So I have identical macro code with only the sheets selected as different statements. So I have 4 copies of the same macro which each marco selects 6 worksheets. So there are 6x4 worksheets. Using your example, it looks like this:

One macro says:
sheets("UserMgtData").select
'do stuff here

Another says:
sheets("IRBData").select
'do exactly the same stuff here
----------------------------
I want one macro where I can prefix the sheets name:
sheets("prefixDATA).select

then somehow pass that prefix to the macro, which may be "Grants" or "IRB" so that the macro makes that prefix substitution on the sheets().select statement.

Sorry for being so unclear, and I appreciate your help.

[sktneer]

May be something like this.....

Assuming the caption of your command button is "Update UserMgt" or "Update IRB".

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim shname As String
Dim n As Integer
For Each ws In Worksheets
'CommanButton1 is the name of CommandButton which is being clicked
shname = Right(CommandButton1.Caption, Len(CommandButton1.Caption) - 7)
On Error Resume Next
n = WorksheetFunction.Search(shname, ws.Name)
On Error GoTo 0
If n > 0 Then
    'do stuff here
End If
n = 0
Next ws
End Sub

Does this help?

[sktneer]

The sample sheet is attached here.

Code:

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim shname As String
Dim n As Integer
For Each ws In Worksheets
'CommanButton1 is the name of CommandButton which is being clicked
shname = Right(CommandButton1.Caption, Len(CommandButton1.Caption) - 7)
On Error Resume Next
n = WorksheetFunction.Search(shname, ws.Name)
On Error GoTo 0
If n > 0 Then
'sample code
    ws.Range("A1") = shname
    'do stuff here
End If
n = 0
Next ws
End Sub

Private Sub CommandButton2_Click()
Dim ws As Worksheet
Dim shname As String
Dim n As Integer
For Each ws In Worksheets
'CommanButton1 is the name of CommandButton which is being clicked
shname = Right(CommandButton2.Caption, Len(CommandButton2.Caption) - 7)
On Error Resume Next
n = WorksheetFunction.Search(shname, ws.Name)
On Error GoTo 0
If n > 0 Then
'sample code
    ws.Range("A1") = shname
    'do stuff here
End If
n = 0
Next ws
End Sub

[jerrynesmith]

Thank you, sktneer. Let me work with your suggestion. I believe you are showing me what I need to know. I'll get back to you.