Carlandrewlewis
asked on
Excel - Auto populate between workbooks
I have data that is to be entered into the 'New Sheet' worksheet of the attahed file. What i have at the moment is when you type in the site name it creats a copy of thetemplate with the site name entered into cell H3. what i would also like it to do is enter the Start date into 'J6' and the week number into cell 'N6' of the copied template. Is it also possible to add a column inbetween B & C of the template that will then show the week commencing date next to each employee name without messing all the code up?
Weekly-Pay-Schedule-Complete-3.xls
Weekly-Pay-Schedule-Complete-3.xls
ASKER
Thats nearly there but i did have a macro that would automatically work out the week start date from the commencment date that would be entered. Also is it possible to have the week commencing date auto to c11:c123??
Hi,
I have added a Select Case statement, depending on the Start Date, it will calculate the Week Commencing Date. I have assumed here that your week begins on a Monday, if it is something other, let me know and I can change the code to suit.
You could also use the Weeknum formula in column D of the New Sheet, rather than the formula you have in. I have shown the formula in cell E2.
When you enter the Start Date, it will then add the sheet.
Bill
Weekly-Pay-Schedule-Complete-3-1.xls
I have added a Select Case statement, depending on the Start Date, it will calculate the Week Commencing Date. I have assumed here that your week begins on a Monday, if it is something other, let me know and I can change the code to suit.
You could also use the Weeknum formula in column D of the New Sheet, rather than the formula you have in. I have shown the formula in cell E2.
When you enter the Start Date, it will then add the sheet.
Bill
Weekly-Pay-Schedule-Complete-3-1.xls
ASKER
Bill thats looking perfect one last thing, is it possible to have the week commencing date auto to c11:c123 in the duplicated sheet?
Hi,
Sorry, there is a line of code;
ActiveSheet.Range("B11:B12 3").Value = dWeekComm
Can you change it to:
ActiveSheet.Range("C11:C12 3").Value = dWeekComm
Bill
Sorry, there is a line of code;
ActiveSheet.Range("B11:B12
Can you change it to:
ActiveSheet.Range("C11:C12
Bill
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Bill sorry for delay, that all looks perfect thanks for your help!! Just one last thing, im gonna post a question regarding timesheet formulas, can you have a look at it for me, i explain what i'm trying to do in the post!!! Thanks again, great help!!!
Would you llike to try the following. I can't run it fully because of the links in your file.
Bill
Weekly-Pay-Schedule-Complete-3-1.xls