Macro to create a Result Sheet from Data and Names Sheet

Hello Experts,

I have a data sheet (sample attached) on Excel 2010.

Data is starting from Row 15
Department names are in Row 14

Data Sheet has details of different departments and Products etc.

I want a macro which should run for each Department on the current Data till the last Row (eg 567 in the attached sample) and last Column (eg AE in the attached sample)

I want to get the following from ‘Data’ Sheet for each Dept as sample shown on the ‘Result’ sheet:

The following Col refers to the ‘Result’ sheet

Col A – should be the current date
Col B – should be Ref No.
Col C – should be a lookup value against the Dept Name from the ‘Names’ Sheet
Col D – should be a text value CUR
Col E – should be the QN value under each Dept. No blanks required
Col F – should be the QV value under each Dept. No blanks required
Col G – should be sum of QN and QV Columns
Col H – should show the Product Name from Col A of Data Sheet
Col I – should show the Dept Name from Row 14 of Data Sheet

I have colored for sake of easy explanation and only shown for Dept1
The macro should do the same for each Dept

Hope to get some help
Thanks in advance
Nasir

Strange, very similar to this request but now required result is the other way round. Coincidences?

http://www.excelforum.com/excel-prog...ult-sheet.html

Alf