F
Ferret via OfficeKB.com
Hi everyone, i'm new to Vba and i'm struggling. the story so far.
i have one workbook with 3 worksheets named - Master Roster, one named - dept
1&3, last one named - dept 2&4. these are leave plots for a four on four off
rotation. the leave plots are for 12 months.
i have used named ranges for the individual
departments for every month i.e. "aprd1" = april dept 1.the ranges includes
the employee number,grading, name. it also includes the leave plot itself of
when they are working or off or when they are on sick, holiday etc.
for each worksheet there are 24 ranges names.
the master roster there has 48 ranged names as the master roster is a
compilation of both the leave plots, and the depatments are in number order i.
e. dept 1, dept2, dept3, dept4.the named ranges on the master roster are like
i.e. "1dapr" this part was graciously solved by Joel afew weeks back.
at present i have 6 columns which are used to calculate "days Taken in Month",
"remaining Leave","
Extra Days Worked","toil daysGiven","Toli days total" in columns (AK,AL,AM,AN,
AO,AP).
the totals for columns (al,an,ap) are running totals from the previous month
using worksheet formulas.
when some body moves depts it means editing the formulas which is time
consuming.
is there away of selecting the previous month (Named Range- APRD1) and
checking the employee number (column "A") with the employee number of the
following month (Named Range-MARD1) if a match found then it copies the data
in that employees row from columns (al,an,ap) to the corresponding employee
row in MARD1.
once done it would then continue to the next row (employee number) if not
found it would continue to the next row only when it hits a blank row would
it stop then continue to the next month (Named Range).
if this is not possible any suggestions would be fantastic
many thanks
Ferret
i have one workbook with 3 worksheets named - Master Roster, one named - dept
1&3, last one named - dept 2&4. these are leave plots for a four on four off
rotation. the leave plots are for 12 months.
i have used named ranges for the individual
departments for every month i.e. "aprd1" = april dept 1.the ranges includes
the employee number,grading, name. it also includes the leave plot itself of
when they are working or off or when they are on sick, holiday etc.
for each worksheet there are 24 ranges names.
the master roster there has 48 ranged names as the master roster is a
compilation of both the leave plots, and the depatments are in number order i.
e. dept 1, dept2, dept3, dept4.the named ranges on the master roster are like
i.e. "1dapr" this part was graciously solved by Joel afew weeks back.
at present i have 6 columns which are used to calculate "days Taken in Month",
"remaining Leave","
Extra Days Worked","toil daysGiven","Toli days total" in columns (AK,AL,AM,AN,
AO,AP).
the totals for columns (al,an,ap) are running totals from the previous month
using worksheet formulas.
when some body moves depts it means editing the formulas which is time
consuming.
is there away of selecting the previous month (Named Range- APRD1) and
checking the employee number (column "A") with the employee number of the
following month (Named Range-MARD1) if a match found then it copies the data
in that employees row from columns (al,an,ap) to the corresponding employee
row in MARD1.
once done it would then continue to the next row (employee number) if not
found it would continue to the next row only when it hits a blank row would
it stop then continue to the next month (Named Range).
if this is not possible any suggestions would be fantastic
many thanks
Ferret