+ Reply to Thread
Results 1 to 2 of 2

Macro to copy values from Sheet1 to Sheet3, based on criteria in Sheet2

  1. #1
    Registered User
    Join Date
    12-17-2009
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    16
    Hello gents,
    in Sheet1 is a database with names, addresses, telephone numbers and so on.
    More or less like this:

    Surname1 Name1 Rank1 Officenumber1 Homenumber1 Address1
    Surname2 Name2 Rank2 Officenumber2 Homenumber2 Address2
    Surname3 Name3 Rank3 Officenumber3 Homenumber3 Address3
    Surname4 Name4 Rank4 Officenumber4 Homenumber4 Address4

    In Sheet2, using a macro people here helped me to fix, i keep notes of people's leaves or absence, like this:
    Everytime someone gets a leave or is away for medical reasons, i select start and end of leave in 2 drop calendars, i select the reason or kind of leave and press a button.
    Then next to the list of names in Sheet2, the cell takes the form of "5 KA-1/11/2010" for example where 5 is the number of days, space and then the type of leave, "-" and finally the first day of his leave. Also each of these cells changes color for the leave type. Exaple, a cell with "KA" is red. A cell with "PA" is green and so on.
    The Sheet2 is like this:

    Surname1 5 KA-7/10/2010 3 PA-1/11/2010
    Surname2 3 PA-5/11/2010
    Surname3 5 KA-1/11/2010
    Surname4 10 FA-25/10/2010 5 KA-15/11/2010 etc.

    Now what i need to do is:
    In order to fill Sheet3 every first of a month which is actually a report for those who where absent even at least one day that given month, i need to have a macro in a button which i will press after selecting a month, so that it will detect who had a change in this selected month and then go in Sheet3 and complete various data from sheet1 and sheet2.
    For example, surname, name, rank, address, a custom text which will describe his period and length of absence etc.
    Then i will print this sheet3 (report).
    Example for October based on "Sheet1" above:

    Surname1 Name1 Rank1 5 days KA to Athens from 7/10/2010 Address1
    Surname4 Name4 Rank4 10 days FA to Rome from 25/10/2010 Address4
    Hope it doesn't sound too scary.
    John

    You are right...
    My workbook will have 15-20 sheets with many functions that will help me at work.
    I left the only sheets needed for this problem i mention here.
    In sheet workbook i get all the data of personnel and it's where i select the dates and type of leave. These get concentrated in the sheet "Adeies".
    You can check it by selecting a name, "from" and "to" dates and "type of leave" in sheet workbook. Then they appear in "ADEIES".
    E1 sheet is the form i have to complete every first of the month. It has only the personnel who were absent the previous month.
    Now, i am trying to find a way, by selecting a month and clicking the button "E1" in workbook, to have in sheet E1 the fields completed. Somehow it must search in sheet ADEIES and move or copy data for those, whose leave started in that given month.
    I hope it's more clear now.
    Thanks again.
    Comment: Just a reminder that the dates in cells in sheet ADEIES, are combined with other data too...
    Attached Files Attached Files
    Last edited by davesexcel; 11-12-2010 at 09:08 AM. Reason: reset replies to zero

  2. #2
    Registered User
    Join Date
    12-17-2009
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Macro to copy values from Sheet1 to Sheet3, based on criteria in Sheet2

    This is also cross posted elsewhere.
    Can't delete the other post.
    Regards
    http://www.mrexcel.com/forum/showthr...12#post2508112
    Last edited by John74; 11-14-2010 at 06:40 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1