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".

Please Login or Register  to view this content.

Does this help?

[sktneer]

The sample sheet is attached here.

Code:

Please Login or Register  to view this content.

[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.