Macro to Consolidate Data from 3 Sheets to a Result Sheet

[rehana402003]

Hello All
I am using excel 2010 and have the problem of Consolidating my Data from 3 Sheets (image attached)

SheetV has got Data (Name1, Name2 etc) from E3 onwards (I have colored it red for sake of explanation)

SheetQ has got same Data (Name1, Name2 etc) from E3 onwards (I have colored it red).

SheetD has the names of the Dept (currently 30+)

I want a macro which should do the following consolidation for me. The macro should create the data as shown in the Result Sheet

Col A1 – Head1 which should copy the current year and month in the format yyyymm
Col B1 – Head2 which should copy the Data in Col B4 till last row from SheetV
Col C1 – Head3 which should map the Dept names from SheetD (eg. Name1 should be replaced with Dept1, Name2 with Dept2 and so on)
Col D1 – Head4 which should put the word CUR till down
Col E1 – Head5 this value will come from SheetQ (colored green for explanation)
Col F1 – Head6 this value will come from SheetV (colored blue for explanation)

The ID Column may also not have the ID numbers in same order (Please see SheetQ and SheetV)

The macro should allow to select the Range E3 onwards viz in the sample provided E3 to G13.

As the sample I have shown is for few rows and in reality it is 2000+ rows of data and 30+ names

Hope to get some help

Thanks in advance

Rehana

 

[rehana402003]

Hello All
Sorry could not get the images posted
So please find here
Hope to get help from someone.
TIA

Excel 2010
	A	B	C	D	E	F	G
1					SheetQ
2
3	ID				Name1	Name2	Name3
4	ID1
5	ID2					5
6	ID3				6	6
7	ID4
8	ID5					8
9	ID6
10	ID7				10
11	ID8					11	11
12	ID9
13	ID10
SheetQ

Excel 2010
	A	B	C	D	E	F	G	H
1					SheetV
2
3	ID				Name1	Name2	Name3
4	ID1
5	ID2					20
6	ID4
7	ID5					25
8	ID3				103	30
9	ID8					311	300
10	ID6
11	ID7				211
12	ID9
13	ID10
14
15		TheID number may be in serial or may not be in serial
16		it may match in order with SheetQ or may not
SheetV

Excel 2010
	A	B
1	Dept Name	Dept No
2	Name1	Dept1
3	Name2	Dept2
4	Name3	Dept3
5	Name4	Dept4
6	Name5	Dept5
7	Name6	Dept6
8	Name7	Dept7
9	Name8	Dept8
10	Name9	Dept9
11	Name10	Dept10
12	Name11	Dept11
13	Name12	Dept12
SheetD

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Head1	Head2	Head3	Head4	Head5	Head6	Head7	Head8	Head9
2	yyyymm	ID1	Dept1	CUR			0
3	yyyymm	ID2	Dept1	CUR			0
4	yyyymm	ID3	Dept1	CUR	6	103	0
5	yyyymm	ID4	Dept1	CUR			0
6	yyyymm	ID5	Dept1	CUR			0
7	yyyymm	ID6	Dept1	CUR			0
8	yyyymm	ID7	Dept1	CUR	10	211	0
9	yyyymm	ID8	Dept1	CUR			0
10	yyyymm	ID9	Dept1	CUR			0
11	yyyymm	ID10	Dept1	CUR			0
12	yyyymm	ID1	Dept2	CUR			0
13	yyyymm	ID2	Dept2	CUR	5	20	0
14	yyyymm	ID3	Dept2	CUR	6	30	0
15	yyyymm	ID4	Dept2	CUR			0
16	yyyymm	ID5	Dept2	CUR	8	25	0
17	yyyymm	ID6	Dept2	CUR			0
18	yyyymm	ID7	Dept2	CUR			0
19	yyyymm	ID8	Dept2	CUR	11	311	0
20	yyyymm	ID9	Dept2	CUR			0
21	yyyymm	ID10	Dept2	CUR			0
22	yyyymm	ID1	Dept3	CUR			0
23	yyyymm	ID2	Dept3	CUR			0
24	yyyymm	ID3	Dept3	CUR			0
25	yyyymm	ID4	Dept3	CUR			0
26	yyyymm	ID5	Dept3	CUR			0
27	yyyymm	ID6	Dept3	CUR			0
28	yyyymm	ID7	Dept3	CUR			0
29	yyyymm	ID8	Dept3	CUR	11	300	0
30	yyyymm	ID9	Dept3	CUR			0
31	yyyymm	ID10	Dept3	CUR			0
Result

 

[rehana402003]

Any help on this please!