implement vlookup formula to VBA code

[karlo via OfficeKB.com]

On the sheet 2 I have table "Orders" where is in column A emplooyes in
column B is month january in column C is month February
and so to the end of year.
On the sheet1 I have pivot table where is emplooyes in row fields, months is
in page fields and one calculated item "working hours" in data fields.
I want copy paste only value calculated item "working hours" from pivot table
to table "Orders"
in adequate column.
If month in pivot table is january must copy value to column B in table
"Orders" if is month May must copy in column F and ect.
I try with formula =if(sheet1!$A$1=1;vlookup(Orders!$A2;sheet!1$A$4:$B$50:
2;false)) but I must manually work too.
Maybe, someone who know how implement this formula to VBA code.

 

[Frederik Vanrenterghem]

On the sheet 2 I have table "Orders" where is in column A emplooyes in
column B is month january in column C is month February and so to the
end of year.
On the sheet1 I have pivot table where is emplooyes in row fields,
months is in page fields and one calculated item "working hours" in
data fields. I want copy paste only value calculated item "working
hours" from pivot table to table "Orders"
in adequate column.
If month in pivot table is january must copy value to column B in table
"Orders" if is month May must copy in column F and ect. I try with
formula =if(sheet1!$A$1=1;vlookup(Orders!$A2;sheet!1$A$4:$B$50:
2;false)) but I must manually work too. Maybe, someone who know how
implement this formula to VBA code.

I guess you could do something along the lines of scrolling through all
the months x, in VBA, with yourPivotTable.TableFields
("month").CurrentPage = x

and each time get the appropriate range using the GetPivotData method:

for each employee y you'd be able to do
set rngResult = yourPT.GetPivotData("working hours","employees",y), with
rngResult.Value at that point being the working hours for one specific
employee in a certain month.

Hope this still helps.

--
Frederik Vanrenterghem | Help me, I'm a prisoner in a Fortune
(e-mail address removed) | cookie file!
http://vanrenterghem.biz |
GPG Fingerprint: |
966B 0E4B 25C1 CC04 E2CB |
8D4C 117F 6469 8925 BDE0 |