Copying cells from existing sheets to new row of master sheet in one workbook

Dear All

I come to this forum with almost no programming skills and less hair than I had before tackling this problem!

I have a workbook that contains n number of sheets each containing evaluation scores which are averaged in specific cells. I would like this average scores from each sheet to automatically create a new row of data in the master sheet. The number of sheets with score varies but code should be able to parse all existing sheets for the same cells and copy them in a new row in the master (1st sheet).

Apart from lacking the coding skills to tackle this problem I also don't know if my Excel 2008 for Mac even allows me to create the necessary code.

Any help or pointing in the right directions is extremely welcomed!

Many thanks

Chico

It can be done in normal PC, but I do not know about Mac. If you want it in PC, please post a sample with a desired result.

Hi AB33

thank you for the prompt reply. I am attaching the spreadsheet in question. So as will hopefully be clear the n number of sheets have 5 cells of average results and a responses count (n) which need to be copied to the Master sheet in a new row. These automatically generated rows are then analysed (weighted average).
Hope this explains my issue.
cheers
C.

Volca!
responses count (n). Where does figure(or row number) in sheets come from?
Are the average formulas which are located in Row 10 are the same for all sheets? Or do they have different row numbers?
I do not know how big is your data, but a simple linking each sheet to the master could do the job (with out macro), assuming you only want to copy a single row from each sheet to the master.

Hi again
not sure if there is missing text from your reply. Row 10 does not change across all data collection sheets. This data is entered by various admin staff in different centers so the cell values need to be fed automatically to a new row of the master sheet. Would the linking do this automatically (and work if sheet with linking is copied and used for new data)? Could this happen without a macro? The responses count (n) is also entered in the same cell across sheets and used in weighting formulas of the mastersheet.
Appreciate your help and time...

C.

Hi kbkumar

sheet names are not known in advance, they reflect internal naming code of different courses which vary from semester to semester. This is why the ideal solution would not rely on specific sheet names or number of sheets (if this is possible).

cheers

C.

Hi kbkumar
I appreciate your time and patience with my issue. I still have not resolved it though. I have changed your first code to output Sheet name to cells A8 onwards instead of J8 (as I want the name to appear in the first column of the master sheet).
You then said to use the indirect formula to link to cell values in respective sheets. However I'm getting a #REF! error. Excuse the silly question but what do you mean by adding A10, ... to C4...?
The cells in Master should look for SheetName stored in column A (same row) and from this find the correct cell in that sheet. I tried all codes found on the net to link via INDIRECT but to no avail. Not sure what I am doing wrong.
thanks
C.

Hi Kbkumar
that is exactly what I wanted the spreadsheet to do but did not have the skills to achieve. A massive thank you for your continued support and above all patience.
Very grateful!
cheers
C.