Run calculations in template for 1 person, copy-paste results, repeat for next person

Hi everyone,

I'm hoping to get some help with some VBA code. I've starting writing parts of it but I'm not sure how to continue. I've attached a sample file.

What code can I use to copy-paste a name (any name except names that start with "ICMTR") from a pivot table in ref!A2: (variable) to a cell (temp!v1), let excel calculate the formulae in the 'temp' worksheet (temp!A2:S53), copy-paste those rows for which sumif(temp!D:Q, ">"$0) to the next blank row in 'data' worksheet. Then repeat for the next name in the pivot table until the last name.

FILE: forum-summarize data.xlsx

I've started with the following but then I got stuck:

Please Login or Register  to view this content.

Thanks a lot for any help you can give me!

Geoff.

I thought I would add some information in case the way I am trying to do this is not the best and you can think of a more efficient way to achieve the same results.

Here is a description of each of the worksheets:

'raw' worksheet:
The 'raw' worksheet is where I paste a set of data. The data is pasted in raw!A1, has 11 columns and a varying amount of rows (a few hundred to several thousands). With a date range that can span a few weeks to a few months.

'temp' worksheet:
The 'temp' worksheet is a template of the operations used to summarize the data in the 'raw' worksheet for a single employee by week (it contains formulas that process the data in the 'raw' worksheet) (it contains 52 weeks for the max data available for an employee in a year).

'ref' worksheet:
The 'ref' worksheet mainly contains a pivot table that lists the unique names of employees found in the raw data worksheet (raw!E:E).

'data' worksheet:
This is where I particularly need some help. This 'data' worksheet is where the data from each unique name is compiled (added one below the other). Each employee has as many rows as weeks for which there is data for them.

I would like to run the formulas in the 'temp' worksheet for each employee listed in the pivot table found in the 'ref' worksheet (expect for the names which begin with "ICMTR"). The way I am trying to do this part means that I have to paste each name in temp!V1, have excel calculate the sheet, and copy the rows (in 'temp' and only those rows where columns D to Q sum up to greater than zero) to the next available row in the 'data' worksheet. Repeat until the employee listed in the pivot table is done.

Hope this is clear and it helps you help me!

Cheers,

Goeff.

I've been trying to piece together all the parts of the code that I need and I've gotten much further but I could still use some help if any of you are able to (I'm not sure if it's a good idea to keep adding new posts to this thread but I will add this last bit showing the progress I've made and see if anyone can help from this point).

The following code tries to cover all three major actions that I need:

1)
'looks for the next employee name in the 'ref' worksheet
'and then copies this name in the 'temp' worksheet
'and then calculate the worksheet

2)
'looks for rows in 'temp' that contain data and copies this row to the next available row in the 'data' worksheet

3)
'loop number 1 above and, within this loop, loop number 2 above ( I am not sure how to put one loop within another)

The code:

Please Login or Register  to view this content.

Hey Everyone,

In case anyone is trying to do something similar, it looks like I may have figured it out. Here is the code:

Please Login or Register  to view this content.

Cheers,

Goeff.