Jun 29 2022 01:37 PM
I have a worksheet that I import several times a day. It lists employees and orders pulled.. What I need to have happen is For each employee (Emp1) it will copy the row and paste it to another worksheet. I need to do this for every employee listed in that column. Any help/suggestions would be greatly appreciated.
Here is a copy of the code I am attempting to use (I know that there is something I am missing):
Sub DiscrepFormReport()
Dim cell As Range
Range("A1").Select
Application.ScreenUpdating = False
'Range("A1") = Sheets("RawData").range("B2))
For Each cell In Range("B2:B200")
If cell <> "" Then
Sheets("RawData").Range("C2:F2").Copy
Sheets("DiscrepencyForm").Range("C2:F2").PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).Select
End If
Next cell
Application.ScreenUpdating = True
End Sub
Here is a copy of my worksheet...
Creation Time | Employee | Item | Item Description | Subinventory | Locator |
06/15/2022 | Emp1 | 97338099 | Product1 | RACK | Loc1 |
06/16/2022 | Emp2 | F35341-410 | Product2 | RACK | Loc2 |
06/16/2022 | Emp3 | 2135QXPA | Product3 | RACK | Loc3 |
06/17/2022 | Emp4 | 54672530 | Product4 | RACK | Loc4 |
06/17/2022 | Emp1 | 39711726 | Product5 | RACK | Loc5 |
06/20/2022 | Emp2 | 5080-638-4 | Product6 | RACK | Loc6 |
06/24/2022 | Emp3 | 47582802001 | Product7 | RACK | Loc7 |
06/24/2022 | Emp4 | 67992834 | Product8 | RACK | Loc8 |
06/23/2022 | Emp1 | 23424922 | Product9 | PRIME | Loc9 |
06/14/2022 | Emp2 | 96016-2 | Product10 | RACK | Loc10 |
06/16/2022 | Emp3 | 9520 | Product11 | RACK | Loc11 |
06/27/2022 | Emp4 | 114802-1 | Product12 | BIN | Loc12 |
06/22/2022 | Emp1 | BL2022 | Product13 | PRIME | Loc13 |
06/21/2022 | Emp2 | 3101G | Product14 | RACK | Loc14 |
Jun 29 2022 10:52 PM
@TeachASU2020 Why insist on using VBA? You can achieve something similar by using a pivot table where you put the employee in the filter field and then use "Show Report filter Pages" in pivot table options to create reports by employee on demand.
If you are on a modern Excel version you could also use UNIQUE and FILTER to achieve overviews by employee.
See attached for some examples.
Jun 30 2022 03:16 AM
Sub DiscrepFormReport()
Dim Emp1 As Worksheet
Dim Emp2 As Worksheet
Dim Emp3 As Worksheet
Dim Emp4 As Worksheet
Dim i As Long
Set Emp1 = Worksheets("Emp1")
Set Emp2 = Worksheets("Emp2")
Set Emp3 = Worksheets("Emp3")
Set Emp4 = Worksheets("Emp4")
Application.ScreenUpdating = False
For i = 2 To 200
If Cells(i, 2).Value = "Emp1" Then
Worksheets("RawData").Range(Cells(i, 3), Cells(i, 6)).Copy
Emp1.Select
Emp1.Range(Cells(i, 3), Cells(i, 6)).PasteSpecial Paste:=xlPasteValues
Worksheets("RawData").Select
Else
If Cells(i, 2).Value = "Emp2" Then
Worksheets("RawData").Range(Cells(i, 3), Cells(i, 6)).Copy
Emp2.Select
Emp2.Range(Cells(i, 3), Cells(i, 6)).PasteSpecial Paste:=xlPasteValues
Worksheets("RawData").Select
Else
If Cells(i, 2).Value = "Emp3" Then
Worksheets("RawData").Range(Cells(i, 3), Cells(i, 6)).Copy
Emp3.Select
Emp3.Range(Cells(i, 3), Cells(i, 6)).PasteSpecial Paste:=xlPasteValues
Worksheets("RawData").Select
Else
If Cells(i, 2).Value = "Emp4" Then
Worksheets("RawData").Range(Cells(i, 3), Cells(i, 6)).Copy
Emp4.Select
Emp4.Range(Cells(i, 3), Cells(i, 6)).PasteSpecial Paste:=xlPasteValues
Worksheets("RawData").Select
Else
End If
End If
End If
End If
Next i
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
Maybe with this code. In the attached file you can click the button in cell H2 to start the macro.