Need help with Macro to generate Monthly OverviewSolved/Closed

Question

Hi all!
I have been trying to build a Macro that allows me to generate a new worksheet with the information from a Master Sheet where i will key in the data. It has to do with attendance taking and I have to generate an overview at the end of the month. However I am struggling with the multiple dates component of the macro as well as due to the number of names I am required to keep track of (about 100)
What I key in in the Master Sheet will be two columns Name and Date. At the end of the month, i am required to generate an overview. Lets call this overview Sheet2. For Sheet2, in the Y axis column, there will be a namelist which has been pre-entered and the X column I will need the dates. 
Take for example, for a Person A, if he was present on the 1st and the 3rd of Jan, I would key in into the Master Sheet as Person A, 1st Jan and another row as Person A, 3rd Jan as shown below:

The overview will require the intersection between Person A name and the date 1st and 3rd to be marked with a 1 and the box to be filled with yellow color and a blank for 2nd Jan due to him being absent. An example is shown below:

So if a person in the Master Name List has been absent for the entire month, he should turn up on the Sheet2 without any "1" indicated. 
Any suggestions on how the code should be like? Much appreciated for any advice!
Sample file:

TrowaD · Answer

Hi ZhengPeng,
My suggestion would be to get rid of the Master sheet and to use Sheet2 to input data. Type a 1 on the days the person worked and let conditonal format colour the cell yellow. That seems faster then inputting the data in the Master sheet. 
Best regards,
Trowa

TrowaD · Answer

Hi ZhengPeng,
OK, then try and see if the code below can help you out.
To implement the code, right-click your Master sheets tab, select View Code and paste the code below in the big white field.
Now confirm an entry in the Master sheet column B (I assumed column A for Persons and column B for dates) and check what happened to sheet2. It should place a 1 in the right place as well as color the cell yellow. Any new entry will be automically processed this way. Hope you like.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mFind As Range
Dim MyCol, MyRow As Integer

If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub

Set mFind = Sheets("Sheet2").Rows(1).Find(Target.Value)
MyCol = mFind.Column

Set mFind = Sheets("Sheet2").Columns("A:A").Find(Target.Offset(0, -1).Value)
mycell = mFind.Row

With Sheets("Sheet2").Cells(mycell, MyCol)
    .Value = 1
    .Interior.Color = 65535
End With
End Sub
Best regards,
Trowa

TrowaD · Answer

Hi ZhengPeng,
That would mean that the date you entered in column B of the Master sheet isn't found in the first row of Sheet2.
When they do match, then consider uploading your workbook (careful with sensitive data) to a free filesharing site like www.speedyshare.com or ge.tt and then post back the download link, so I can take a look.
Here is the code which shows you a message box instead of the error message in case either the data or the person can't be found on sheet2:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mFind As Range
Dim MyCol, MyRow As Integer

If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub

Set mFind = Sheets("Sheet2").Rows(1).Find(Target.Value)
If mFind Is Nothing Then
    MsgBox "The date entered can't be found on Sheet2. Data not processed."
    Exit Sub
End If
MyCol = mFind.Column

Set mFind = Sheets("Sheet2").Columns("A:A").Find(Target.Offset(0, -1).Value)
If mFind Is Nothing Then
    MsgBox "The Person corresponding to the date entered can't be found on Sheet2. Data not processed."
    Exit Sub
End If
MyRow = mFind.Row

With Sheets("Sheet2").Cells(MyRow, MyCol)
    .Value = 1
    .Interior.Color = 65535
End With
End Sub
Best regards,
Trowa

TrowaD · Answer

Hi ZhengPeng,
Code has been adjusted.
Msgbox has been replaced by the use of red cells. Either the date, person or both will become red cells when no match is found.
Code can be used in combination with a button. My advise is to put the code in a standard module and use the code line "Call RunMe" for the Button code. This way you keep your codes in one place (modules) instead of spread across multiple sheets.
As to why, you probably see the difference in code. The first code had a Target range to refer to something that has changed. Now that we don't use the Worksheet_Change event anymore, we have to replace the Target variable.
Here is the code:
Sub RunMe()
Dim mFind As Range
Dim MyCol, MyRow As Integer

For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Set mFind = Sheets("Sheet2").Rows(1).Find(cell.Value)
    If mFind Is Nothing Then
        Cells(cell.Row, "B").Interior.Color = 255
        MyCol = vbNullString
    Else
        MyCol = mFind.Column
    End If
    
    Set mFind = Sheets("Sheet2").Columns("A").Find(cell.Offset(0, -1).Value)
    If mFind Is Nothing Then
        Cells(cell.Row, "A").Interior.Color = 255
        Exit Sub
    End If
    MyRow = mFind.Row
    
    If MyCol <> vbNullString Then
        With Sheets("Sheet2").Cells(MyRow, MyCol)
            .Value = 1
            .Interior.Color = 65535
        End With
    End If
Next cell
End Sub
Best regards,
Trowa 
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

Need help with Macro to generate Monthly Overview

4 responses

Similar discussions

Subscribe To Our Newsletter!