I have a planning worksheet (sheet1) with number of vba codes (command buttons) that performs various task. I am now trying to add another function which is to extract and generate a 4 weeks summary (Sheet3) from sheet1 anytime the command button is clicked.
This is how the code on sheet3 will work:
Dim totalMan As Integer
Set planWeek = Sheet1.Range("lw58:ma58")
For Each weekDay In planWeek
If Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 1 Then
Sheet3.Range("d8").Value = 1
ElseIf Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 2 Then
Sheet3.Range("d8").Offset(0, 1).Value = 1
ElseIf Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 3 Then
Sheet3.Range("d8").Offset(0, 2).Value = 1
ElseIf Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 4 Then
Sheet3.Range("d8").Offset(0, 3).Value = 1
ElseIf Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 5 Then
Sheet3.Range("d8").Offset(0, 4).Value = 1
End If
Next
This is how the code on sheet3 will work:
- Extract all task (excluding workers column) from sheet1 and paste to sheet3
- Extract ONLY four weeks (from today) from sheet1 and paste it to sheet3.
- Extract weekdata (all "7.5") into sheet3 so that for every "7.5" it will be "1" in a single cell in a task row in sheet3. For example, if there are 3 men (rows) under a task (7.5 hours) on Monday, it should be "3" in a single cell corresponding to the day of the week in Sheet3. In other words, write the total number of men that work that day in Sheet3.
- Print sheet3 to PDF, etc.
Dim totalMan As Integer
Set planWeek = Sheet1.Range("lw58:ma58")
For Each weekDay In planWeek
If Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 1 Then
Sheet3.Range("d8").Value = 1
ElseIf Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 2 Then
Sheet3.Range("d8").Offset(0, 1).Value = 1
ElseIf Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 3 Then
Sheet3.Range("d8").Offset(0, 2).Value = 1
ElseIf Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 4 Then
Sheet3.Range("d8").Offset(0, 3).Value = 1
ElseIf Application.WorksheetFunction.CountA(weekDay) > 0 & weekDay = 5 Then
Sheet3.Range("d8").Offset(0, 4).Value = 1
End If
Next