Hello everybody,
I use a PC with windows 7 64bit and office/excel 2010. I already tried to search the forum, but didn't find anything related to this matter, or didn't know the right keywords...
Recently, I inherited a few macro files, that I am trying to "clean-up" or improve now. The first thing I'd like to do is to "condense" the code, get rid-off all redundancies, and add more structure to the files (sometimes as basic as renaming macros instead of macro 1, 2, 3...).
Long story short:
The current project I am working on has several command buttons. Each command button triggers an "update" macro for a particular Region. The macros are essentially identical with a few minor differences. I am looking for a way to create one mainUpdate module and refer to it in every regional module.
This means when I click command button "REGION1"
I would "jump" to call REGION1
here my issue, the code of the macro I am running uses particular file references, to open and save files. At the moment these references are "hard-coded". I would like to change:
Workbooks.Open Filename:= _
"Z:\Folder\REGION1.xlsm"
To something that would allow me to open / save the file as the respective region (depending which commandbutton I click (So 10 commandbuttons> REGION1....REGION10)
If I click on commandbutton REGION1 I call sub REGION1()
Is there a way I can substitute the REGION1 in the path above with some kind of check or reference to the command button, so I can maintain the entire code with the exception of the path?
I don't know which function/command I could use here.
The second difference is that at a later stage (where an automated e-mail is generated) REGION1 references a cell or rather a range: RANGE(A60), REGION2 would reference RANGE(A61)...
Could I setup some kind of check that gives me the REGION and respective value of the RANGE? Would I have to setup and external Table, or what is the best solution?
.TO = ActiveWorkbook.Worksheets("Approval").Range("A610").Value
I hope I posted all relevant information for you. If you need additional info, please let me know and thanks in advance for every help!
I use a PC with windows 7 64bit and office/excel 2010. I already tried to search the forum, but didn't find anything related to this matter, or didn't know the right keywords...
Recently, I inherited a few macro files, that I am trying to "clean-up" or improve now. The first thing I'd like to do is to "condense" the code, get rid-off all redundancies, and add more structure to the files (sometimes as basic as renaming macros instead of macro 1, 2, 3...).
Long story short:
The current project I am working on has several command buttons. Each command button triggers an "update" macro for a particular Region. The macros are essentially identical with a few minor differences. I am looking for a way to create one mainUpdate module and refer to it in every regional module.
This means when I click command button "REGION1"
I would "jump" to call REGION1
here my issue, the code of the macro I am running uses particular file references, to open and save files. At the moment these references are "hard-coded". I would like to change:
Workbooks.Open Filename:= _
"Z:\Folder\REGION1.xlsm"
To something that would allow me to open / save the file as the respective region (depending which commandbutton I click (So 10 commandbuttons> REGION1....REGION10)
If I click on commandbutton REGION1 I call sub REGION1()
Is there a way I can substitute the REGION1 in the path above with some kind of check or reference to the command button, so I can maintain the entire code with the exception of the path?
I don't know which function/command I could use here.
The second difference is that at a later stage (where an automated e-mail is generated) REGION1 references a cell or rather a range: RANGE(A60), REGION2 would reference RANGE(A61)...
Could I setup some kind of check that gives me the REGION and respective value of the RANGE? Would I have to setup and external Table, or what is the best solution?
.TO = ActiveWorkbook.Worksheets("Approval").Range("A610").Value
I hope I posted all relevant information for you. If you need additional info, please let me know and thanks in advance for every help!
