VB Excel Macro That Edits Itself Code

Hey Everyone,

I haven't decided to tackle this yet, but I'm just wondering if it is possible to create an Excel VB Macro that can alter/edit the code of another Macro? Maybe there's another way around it, but this is the scenario that has promted me to think about this:

Each day I recieve production reports. At the end of each month I pull certain data from these reports to create a monthly report. Sometimes the format of these reports are different, as a result I've had to construct an extensive VB macro that searches for various rows and columns, and does several complicated checks on each daily report to ensure that the data I am pulling is correct.

As it stands right now Macro 1 is pulling information about Items A & B, but I know new items will be added to these reports. So lets say in 2 months Item C is added, then D, and eventually I have new items being added on a monthly basis.

Is it possible to create a macro that alters the original Macro 1 in such a way that it will begin to pull the new information on new items?

Thanks to everyone for your input.

Best way to do something like that would be to create a range in an empty worksheet

And then in the macro create an array holding those values (Items as you describe them)

Please Login or Register  to view this content.

This sheet can be updated from a macro or manually

In the macro doing all the various things you then create an array

Please Login or Register  to view this content.

Now you can create a loop that does whats required for each item.

Hello ashleys.nl,

Yes you can modify a macro in place with another macro. This macro reads the updated macro text from a file. The entire macro is deleted and then replaced with file contents. This assumes that the macro is always located in the same VBA Module or Worksheet within the active workbook.

Change the file path, file name, module name, and macro name to match what you are using. The macro can be run either automatically or manually. I think automatically would be best. It could be run when the workbook opens and insures that the macro is updated before it is used.

Please Login or Register  to view this content.