Copy and paste with vba

Copper Contributor

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 TimeEmployeeItemItem DescriptionSubinventoryLocator
06/15/2022Emp197338099Product1RACKLoc1
06/16/2022Emp2F35341-410Product2RACKLoc2
06/16/2022Emp32135QXPAProduct3RACKLoc3
06/17/2022Emp454672530Product4RACKLoc4
06/17/2022Emp139711726Product5RACKLoc5
06/20/2022Emp25080-638-4Product6RACKLoc6
06/24/2022Emp347582802001Product7RACKLoc7
06/24/2022Emp467992834Product8RACKLoc8
06/23/2022Emp123424922Product9PRIMELoc9
06/14/2022Emp296016-2Product10RACKLoc10
06/16/2022Emp39520Product11RACKLoc11
06/27/2022Emp4114802-1Product12BINLoc12
06/22/2022Emp1BL2022Product13PRIMELoc13
06/21/2022Emp23101GProduct14RACKLoc14
      
2 Replies

@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.

@TeachASU2020 

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.