Copy and paste with vba

Question

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

riny_van_eekelen · Answer

TeachASU2020&nbsp;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.&nbsp;If you are on a modern Excel version you could also use UNIQUE and FILTER to achieve overviews by employee.&nbsp;See attached for some examples.

oliverscheurich · Answer

TeachASU2020&nbsp;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 SubMaybe with this code. In the attached file you can click the button in cell H2 to start the macro.&nbsp;

Forum Discussion

Copy and paste with vba

2 Replies

Resources