VBA Problem, best way to approach it.

[DeanRobinson]

Hi all, trying to work out the best way of approaching this issue, Currently i have an office manager that collates 18 daily sheets from his team, all he has to do is copy and paste them onto one sheet which is the end of day sheet, then once he has done that sort by location which would be a county, ie, KENT, SUSSEX, LONDON, MIDLAND once he is done then copy and paste that section onto the end of that areas weekly sheet. the problem ive got is i copying and pasting the info shouldnt be to hard, as i could call the weekly sheet find last row then copy values from one row below that, and use the location to decide what sheet to call, but the problem is the directory path, first id want the code to be able to look at the date and decide what sheet its going in ie 3rd September for London will go into p:/user/Dean/Documents/Paul_Street/London/September/Week_1.

and say the 8th would go into week 2.

but say that the manager from that area changed so Paul_Street became Joe_Bloggs id want the user to be able to change the directory path without it coming back to me. any ideas would be great,