Solved

Excel - Auto populate between workbooks

Posted on 2008-10-21
7
437 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Carlandrewlewis
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:WJReid
ID: 22768274
Hi,
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
0
 

Author Comment

by:Carlandrewlewis
ID: 22768432
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??  
0
 
LVL 13

Expert Comment

by:WJReid
ID: 22768703
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
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:Carlandrewlewis
ID: 22768867
Bill thats looking perfect one last thing, is it possible to have the week commencing date auto to c11:c123 in the duplicated sheet?
0
 
LVL 13

Expert Comment

by:WJReid
ID: 22768949
Hi,
Sorry, there is a line of code;
ActiveSheet.Range("B11:B123").Value = dWeekComm
Can you change it to:
ActiveSheet.Range("C11:C123").Value = dWeekComm

Bill
0
 
LVL 13

Accepted Solution

by:
WJReid earned 500 total points
ID: 22769139
Hi carlandrewlewis,

I have added a few things to your template, just cosmetic for appearance, so that all of the value errors and zero values will not show. I also added conditional formatting in column C of the Template so that the text will be white unless there is a name in column D.

Bill
Weekly-Pay-Schedule-Complete-3-1.xls
0
 

Author Closing Comment

by:Carlandrewlewis
ID: 31508330
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!!!
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question