Creating a MACRO that copies data from one work sheet, and 'appends' to another sheet.

Hi,

I am very new to MACROS but working on an economic model. I have two requests:

1, Basically, I want to have a MACRO that would copy all data from a worksheet titled 'Data' into a sheet within the same excel file called 'Data results'. Everytime I run my excel file, it gives me a new set of results in sheet 'Data'. I therefore want to keep appending the results as 'values' only, to the sheet 'Data results' with a blank row in between two sets of results or just the next row available.

The rows I need to copy from 'Data results' are B5: AP68.

2, Is it possible to create a 'button' within the excel file, for e.g. in a sheet called 'execute' that triggers the MACRO, instead of going into the 'developer' mode every time?

Looking to do this really soon (hopefully today) and I am sure there are lots of MACRO gurus out there who can help I hope. I am attaching a sample excel file to illustrate.

Thanks,
Ali

Thanks arlu1201

This works like a charm. I just tried it and perfect!

Can the MACRO be made more intelligent by some logic that would only copy scenario X once. It works perfectly fine now. I can copy as values results from my 'Data' sheet into 'Data results' sheet. But if I press 'update' button more than once for the same scenario, (for e.g. 3 times), then it would copy the same results thrice. Just trying to understand if that is really how it should be?

If yes, then no worries.

I have also often seen consolidated results in excel, denoted by colors. where red means not good results and green means positive results and there are varying colors in between. Would you also know how to create that?

I think I will let it be the way it is. In my business model, every scenario will be distinct so its best to copy all results.

I have one more request w.r.t the second point I raised above. I have again made a template. So now what I want to do is everytime I copy my results into 'data results', I would like the sheet 'Execute' to compute the table I have shown.

This is how it works.

Step 1. I run a scenario
Step 2. Macro copies everything into 'data results'
Step 3. Another MACRO or code, updates table shown in sheet 'execute'. It should pick firstly the scenario (number 4 in this example), and secondly, all 'new price plans' from column 'E' on 'data results', where new price plans are F and beyond. A to E are old.
Step 4. The same MACRO should then update the 'total revenues' table by traversing through the 'data results' sheet, scenario 4, and look for string 'total revenues' and then add it to the cell 'L7' on 'execute.
Step 5. Here, can we add some conditional formating. I can then asign values. if less than 99999 give it a red color. if greater than 99999 give it a green color.

Is this possible at all?

---------- Post added at 01:46 PM ---------- Previous post was at 01:44 PM ----------

I could not attach the excel file here. Can we only attach one file per thread?

Here is the file.