Essentially I need to move data from one sheet to another in Excel. I have attached photos of the data sheet (that I need to pull data from) and the destination sheet(that I need to post the data to). I need to write a code to read the sheet, and create a new row for each activity(cleaning, mopping, scrubbing, wiping, etc) with the correct # of hours they spent on each, how many units they completed, their name, and the year. I have attached a photo of a few lines that I completed manually, but if I could automate the process it would be a lot easier. Thanks so much for any help you can offer :)
So this is not the cleanest code that I've ever done, but I believe it will fill out the parts of it that I understand and you can fill in the rest.
Since the employees are all on the same sheet underneath one another, it loops from i down to the bottom of the sheet. It checks for "PP" in the cell text and if it finds it, it will start checking that row for units/hours to transfer. If the type on that row has no error, it will copy the data for that type over. It repeats this for each type.
If there is no "PP" in the cell text on sheet one, then it will alternatively check for "Employee" name so that it knows whose name to copy onto sheet two for the following entries.
Now the code is really ugly because I have it continually specify each cell to grab the values from it. This was the quickest way for me to grab everything you list on your first sheet while making this easily update-able by you in the future.
Sub Report()
Dim ws1 As Worksheet
Set ws1 = Sheets(1)
Dim ws2 As Worksheet
Set ws2 = Sheets(2)
Dim i As Long
Dim row_current As Long
Dim employee_name_row As Long: employee_name_row = 1
For i = 1 To ws1.Cells(ws1.Rows.count, "A").End(xlUp).row
If InStr(ws1.Cells(i, "A").Value2, "PP") > 0 Then
'getting row to use on sheet 2
row_current = ws2.Cells(ws2.Rows.count, "A").End(xlUp).Offset(1, 0).row
If Not IsError(ws1.Cells(i, "D")) Then 'Cleaning
'period, name
ws2.Cells(row_current, "A").Value2 = ws1.Cells(i, "A").Value2
ws2.Cells(row_current, "B").Value2 = ws1.Cells(employee_name_row, "B").Value2
'task, units, hours
ws2.Cells(row_current, "C").Value2 = ws1.Cells(3, "B").Value2
ws2.Cells(row_current, "E").Value2 = ws1.Cells(i, "C").Value2
ws2.Cells(row_current, "F").Value2 = ws1.Cells(i, "B").Value2
'year
ws2.Cells(row_current, "I").Value2 = ws1.Cells(employee_name_row, "L").Value2
row_current = row_current + 1
End If
If Not IsError(ws1.Cells(i, "G")) Then 'Mopping
'period, name
ws2.Cells(row_current, "A").Value2 = ws1.Cells(i, "A").Value2
ws2.Cells(row_current, "B").Value2 = ws1.Cells(employee_name_row, "B").Value2
'task, units, hours
ws2.Cells(row_current, "C").Value2 = ws1.Cells(3, "E").Value2
ws2.Cells(row_current, "E").Value2 = ws1.Cells(i, "F").Value2
ws2.Cells(row_current, "F").Value2 = ws1.Cells(i, "E").Value2
'year
ws2.Cells(row_current, "I").Value2 = ws1.Cells(employee_name_row, "L").Value2
row_current = row_current + 1
End If
If Not IsError(ws1.Cells(i, "J")) Then 'Scrubbing
'period, name
ws2.Cells(row_current, "A").Value2 = ws1.Cells(i, "A").Value2
ws2.Cells(row_current, "B").Value2 = ws1.Cells(employee_name_row, "B").Value2
'task, units, hours
ws2.Cells(row_current, "C").Value2 = ws1.Cells(3, "H").Value2
ws2.Cells(row_current, "E").Value2 = ws1.Cells(i, "I").Value2
ws2.Cells(row_current, "F").Value2 = ws1.Cells(i, "H").Value2
'year
ws2.Cells(row_current, "I").Value2 = ws1.Cells(employee_name_row, "L").Value2
row_current = row_current + 1
End If
If Not IsError(ws1.Cells(i, "M")) Then 'Wiping
'period, name
ws2.Cells(row_current, "A").Value2 = ws1.Cells(i, "A").Value2
ws2.Cells(row_current, "B").Value2 = ws1.Cells(employee_name_row, "B").Value2
'task, units, hours
ws2.Cells(row_current, "C").Value2 = ws1.Cells(3, "K").Value2
ws2.Cells(row_current, "E").Value2 = ws1.Cells(i, "L").Value2
ws2.Cells(row_current, "F").Value2 = ws1.Cells(i, "K").Value2
'year
ws2.Cells(row_current, "I").Value2 = ws1.Cells(employee_name_row, "L").Value2
row_current = row_current + 1
End If
If Len(ws1.Cells(i, "N")) > 0 Then 'Jumping
'period, name
ws2.Cells(row_current, "A").Value2 = ws1.Cells(i, "A").Value2
ws2.Cells(row_current, "B").Value2 = ws1.Cells(employee_name_row, "B").Value2
'task, hours
ws2.Cells(row_current, "C").Value2 = ws1.Cells(3, "N").Value2
ws2.Cells(row_current, "F").Value2 = ws1.Cells(i, "N").Value2
'year
ws2.Cells(row_current, "I").Value2 = ws1.Cells(employee_name_row, "L").Value2
row_current = row_current + 1
End If
If Len(ws1.Cells(i, "O")) > 0 Then 'Swimming
'period, name
ws2.Cells(row_current, "A").Value2 = ws1.Cells(i, "A").Value2
ws2.Cells(row_current, "B").Value2 = ws1.Cells(employee_name_row, "B").Value2
'task, hours
ws2.Cells(row_current, "C").Value2 = ws1.Cells(3, "O").Value2
ws2.Cells(row_current, "F").Value2 = ws1.Cells(i, "O").Value2
'year
ws2.Cells(row_current, "I").Value2 = ws1.Cells(employee_name_row, "L").Value2
row_current = row_current + 1
End If
If Len(ws1.Cells(i, "P")) > 0 Then 'Other
'period, name
ws2.Cells(row_current, "A").Value2 = ws1.Cells(i, "A").Value2
ws2.Cells(row_current, "B").Value2 = ws1.Cells(employee_name_row, "B").Value2
'task, hours
ws2.Cells(row_current, "C").Value2 = ws1.Cells(3, "P").Value2
ws2.Cells(row_current, "F").Value2 = ws1.Cells(i, "P").Value2
'year
ws2.Cells(row_current, "I").Value2 = ws1.Cells(employee_name_row, "L").Value2
row_current = row_current + 1
End If
If Len(ws1.Cells(i, "Q")) > 0 Then 'Computer Probs
'period, name
ws2.Cells(row_current, "A").Value2 = ws1.Cells(i, "A").Value2
ws2.Cells(row_current, "B").Value2 = ws1.Cells(employee_name_row, "B").Value2
'task, hours
ws2.Cells(row_current, "C").Value2 = ws1.Cells(3, "Q").Value2
ws2.Cells(row_current, "F").Value2 = ws1.Cells(i, "Q").Value2
'year
ws2.Cells(row_current, "I").Value2 = ws1.Cells(employee_name_row, "L").Value2
row_current = row_current + 1
End If
ElseIf InStr(ws1.Cells(i, "A").Value2, "Name") > 0 Then
employee_name_row = i
End If
Next i
End Sub