add new condition in code

**barkarlo** · 02-07-2009, 04:09 PM

Please Login or Register  to view this content.

In the sheets "drivers" there is pivot table from which code copy column "working hours"(range d5:d36) to sheets "overtime". If case 1 paste to range b2:b50, if case 2 paste to range c2:c50 and etc.
Because data in pivot table not static sometimes list of employees and their
working hours is not correspond with list of employees in the sheet "overtime".
(range A2:A50). In this case code misinserting value of working hours.
for example: employee John is secondary in range b2:b50 in the sheets "overtime". He can be tertiary in the pivot table on the sheet "drivers" and this code their working hours paste another employee.
I need help, how can I add one condition in this code who will compare list of employees from sheets "overtime" with range of employees from pivot table in the sheets "drivers".
After that code will inserted value of working hours correct.
Thanks,

**broro183** · 02-07-2009, 07:29 PM

I suggest trying a vlookup (or maybe a "Getpivotdata") formula in the spreadsheet before attempting to code it into a macro. For further details on Vlookup check the Excel Help Files. Once you are happy with a formula approach you can could then convert it into a macro...

hth
Rob

**barkarlo** · 02-08-2009, 11:43 AM

Please Login or Register  to view this content.

I try with this vlookup function but I don't know how can I copy value without formula.
In the table overtime "employees" is column header A , month January is column header B , february is column header C and etc.
This formula is OK when I copy working hours of employees to column B.
But,when I change month in the pivot table and try copy data to column C
data in column B change value too.
Have you some idea for solve this problem?

**broro183** · 02-09-2009, 03:53 AM

Once you have the values for each month using the vlookup, you can then copy & paste special over the respective column as values. This will be quite similar to what you were attempting to do with the initial use of vlookup preventing the chance of "misinserting value of working hours".

btw,
an easy way to convert to values in code without using Paste Special (once the range contains the vlookup & the sheet has calculated) is:

Please Login or Register  to view this content.

If you want, you can post a small example file with no confidential data etc & I'll see if I can include it in your existing macro code...

hth
Rob

**barkarlo** · 02-09-2009, 12:18 PM

Month 1
Drivers Working hours.
Antony 178
Jansen 123
John 153
Paul 152
Ted 164
Grand Total 770
Month 2
Drivers Working hours.
Antony 172
Elliot 168
John 158
Mark 165
Ted 136
Grand Total 799

This is data from pivot table for month January and February.Can be seen
that the order of drivers isn't same in month January as month February.
In the sheets "overtime" I have list all employees.
When I want copy paste data range for month February code must put data
in adequate cells.
As etc. code must write data in all correspond cells.In a cells Jansen must write 0 or jump and in cells Elliot must write 168.