VBA Code

[steveautsin99]

Hi,

I have a workbook called overlook.xlsx and in sheet1 of this work book I have the data for all my ten teams. Please have a look at just TeamA in this sheet.

Excel Workbook
	B	C	D	E	F
2	Team A		Planned	Offered	Achieved
3	Yesterday	Parameter1
4	26-Feb-11	Parameter2
5		Parameter3
6	Current Week	Parameter1
7	19 February to 25 February	Parameter2
8		Parameter3
9	Previous Week	Parameter1
10	12 February to 18 February	Parameter2
11		Parameter3
Sheet1

The data for overlook.xlsx is taken from a workbook called metrics.xlsx. Metrics.XLSX has ten sheets for then ten teams. Each sheet in Metrics.XLSX have the data for one team and this data is for the previous 12 months.Now I need a VBA code which would copy the data from metrics .XLSX and paste it in the corresponding team cells in Overlook.xlsx.
For Ex say yesterday was 26-Feb-11
The code should copy the data from row no-163 (Not the entire row only the data that hast to be pasted in overlook.xlsx.
For current week the data from the row no 165
And for previous week data from row no 157 should be copied and pasted in overlook.xlsx.

Please have a look on metrics .xlsx . It is just one sheet .

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
2	Nov-10	Day	PP1	OP1	AP1	PP2	PP3	OP2	OP3	AP2	AP3
7	2010	December
8	2011	January
9	2011	February
10
11	Nov-10	Day	PP1	OP1	AP1	PP2	PP3	OP2	OP3	AP2	AP3
152	16-Feb-11	Monday
153	17-Feb-11	Tuesday
154	18-Feb-11	Wednesday
155	19-Feb-11	Thursday
156	20-Feb-11	Friday
157	Week	Week 44	367,830	367,954	367,836	118	123	124	367,954	367,954	367,954
158	21-Feb-11	Saturday
159	22-Feb-11	Sunday
160	23-Feb-11	Monday
161	24-Feb-11	Tuesday
162	25-Feb-11	Wednesday
163	26-Feb-11	Thursday
164	27-Feb-11	Friday
165	Week	Week 44	367,830	367,954	367,836	118	123	124	367,954	367,954	367,954
sheet1

Please help me with a vba code to perform this

 

[steveautsin99]

Hi

Any update on this ?

 

[baodinh]

Steve, I don't think you need VBA for this one. VLOOKUP formula should work just fine. Currently your week numbers are the same ie Week 44. You need to make the weeks unique for the VLOOKUP formula. If needed, you can always create a new sheet with each day corresponding to a week number to use in your lookup formula.