Using string variables to call another macro

I have a macro that is in a “control workbook”. When triggered it get a worksheet from one folder and copies it into itself. Then it runs another macro which formats the sheet and then moves that worksheet to another folder where it will later be mailed along with some other worksheets.

First, I feel that my code in the this top macro could be simplified and written better than I have it now. I would appreciate any advice.

Secondly, I have a couple of other buttons which will be doing the same type of thing as the first except they will be looking for a different excel file and calling another macro which will format the worksheet a little differently.

What I was thinking of doing is using saying some like A= File A, B= File B and C= File C and X= Macro X, Y=Macro Y, and Z=Macro Z etc. Then if Button 1 is pressed, it will use A and X if Button 2 is pressed use B and Y etc.


What would be the best way to write this?

I appreciate any help. I'll be out of my office on Tuesday but will respond on Wednesday.

Thank you so much. That works great. The only reason that I was thinking of using variables is that the column of buttons on this worksheet is only for one location. There are three other locations and that number could grow.

Thank you. I really appreciate this. So for the next location I would just have another sub like :

Sub location2ACSReport()
ProcessReport "C:\Users\chs103233\Documents\reports\location2\", "ACS Sample Report.xlsb", _
"C:\Users\chs103233\Documents\reports\location2 mail\", "AgentCallSummaryReport.xlsx", _
"formatACS", "Agent Call Summary Report has been processed."
End Sub

Another question - in the format sub that I call from this macro, I have a report title that comes up. How would I change it?

.

.

End With

Range("A1:R1").Value = "Contact Service Queue Activity Report-DeerFoot"


'Row 2 ----------------------------------------------------------------------------------------