I am fairly new to VBA, but I can normally worm my way through. I am stumped here. Any help is greatly appreciated.
I have a report in sheet "day" that in column H1:Q1 I have employee names, in H2:Q Finalrow I have hours worked, in column D2-G Finalrow I have a list of information they worked on.
I need to loop through each column(H-Q) by row and copy into a separate sheet called "input" to create a database to pivot.
I need it to take the employees name from "day" and put in column E in the "input" sheet, then take the hours worked from "Day" column H and put in Column F in the "input" sheet, then take the information in column D-G in the "day" sheet and copy in column A-D on the "input" sheet, and go through each row in and each column. It is basically re-arranging the sheet "day".
I got it to do one column, but couldn't make it copy to next available row on "Input" sheet, it kept overwriting the same 1st row.
This is what I have, I had it only copying if hours worked were more than 0:
Sub test()
finalrow = Range("f65536").End(xlUp).Row
For i = 2 To finalrow
If Cells(i, 9).Value > 0 Then
Cells(1, 9).Copy
Worksheets("input").Activate
finalrow1 = Range("e65536").End(xlUp).Row
Range("e" & finalrow1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("day").Activate
Cells(i, "d").Resize(, 4).Copy
Worksheets("input").Activate
finalrow2 = Range("a65536").End(xlUp).Row
Range("a" & finalrow2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("day").Activate
End If
Next i
End Sub
I have a report in sheet "day" that in column H1:Q1 I have employee names, in H2:Q Finalrow I have hours worked, in column D2-G Finalrow I have a list of information they worked on.
I need to loop through each column(H-Q) by row and copy into a separate sheet called "input" to create a database to pivot.
I need it to take the employees name from "day" and put in column E in the "input" sheet, then take the hours worked from "Day" column H and put in Column F in the "input" sheet, then take the information in column D-G in the "day" sheet and copy in column A-D on the "input" sheet, and go through each row in and each column. It is basically re-arranging the sheet "day".
I got it to do one column, but couldn't make it copy to next available row on "Input" sheet, it kept overwriting the same 1st row.
This is what I have, I had it only copying if hours worked were more than 0:
Sub test()
finalrow = Range("f65536").End(xlUp).Row
For i = 2 To finalrow
If Cells(i, 9).Value > 0 Then
Cells(1, 9).Copy
Worksheets("input").Activate
finalrow1 = Range("e65536").End(xlUp).Row
Range("e" & finalrow1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("day").Activate
Cells(i, "d").Resize(, 4).Copy
Worksheets("input").Activate
finalrow2 = Range("a65536").End(xlUp).Row
Range("a" & finalrow2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("day").Activate
End If
Next i
End Sub