Copy a column and paste to a different column in a different sheet

I tried using the code below to export from another sheet to a different column in a another sheet and getting an error:

my full code is: (the red code lines will be the ones I am getting the error from)

Public Sub getStat()
    Dim cel As Range, wsLog As Worksheet, wsStatus As Worksheet
    Dim proc_file As String, wsProc As Worksheet
    Dim xlProc As Workbook, found As Range, nextrow As Long
    
    Set wsLog = Workbooks("Request Template.xlsm").Sheets("Log")
    Set wsStatus = Workbooks("Request Template.xlsm").Sheets("Status")
    
    wsStatus.UsedRange.Offset(1).ClearContents
    
    wsLog.UsedRange.Offset(5, 0).AutoFilter Field:=5, Criteria1:="<>Disregard Request"
    
    proc_file = "C:\Users\user1\Documents\Projects\Requests\Request Processing Main.xlsm"
    
    Application.ScreenUpdating = False
    Set xlProc = Workbooks.Open(proc_file)
    Set wsProc = xlProc.Sheets("Status")
    

    For Each cel In Intersect(wsLog.UsedRange.Offset(2).Resize(wsLog.UsedRange.Rows.Count - 1), wsLog.Columns(1)).SpecialCells(xlCellTypeVisible)
    nextrow = wsStatus.Cells(wsStatus.Rows.Count, 1).End(xlUp).Row + 1
    Set found = wsProc.Columns(1).Find(cel, lookat:=xlWhole)
    If Not found Is Nothing Then
    wsProc.Range("E" & found.Row).Copy wsStatus.Range("A" & nextrow)
    wsProc.Range("B" & found.Row).Copy wsStatus.Range("B" & nextrow)
    wsProc.Range("D" & found.Row).Copy wsStatus.Range("D" & nextrow)
    wsProc.Range("C" & found.Row).Copy wsStatus.Range("E" & nextrow)
    wsProc.Range("A" & found.Row).Copy wsStatus.Range("F" & nextrow)
    Next
    
    xlProc.Close 0
    wsStatus.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8), Header:=xlYes
    wsLog.UsedRange.AutoFilter
    Application.ScreenUpdating = True
    wsStatus.Activate
    
End Sub

What is the error message? and which specific line is highlighted when you click debug?

here are the images of the error
Capture2.PNGCapture1.PNG

The images did not come through, but maybe this will help. In this:

Set found = wsProc.Columns(1).Find(cel, lookat:=xlWhole)

line of code, change to

Set found = wsProc.Columns(1).Find(cel.Value, lookat:=xlWhole)

tried to attached the images to a spreadsheet. see errors.xlsx

Originally Posted by JLGWhiz

Since every variable in that line had previously been used without error, you need to use your mouse pointer while in debug mode and hover over each variable to see which one lost its value. The defective variable should show "Nothing" as a value in the tool tips box while the mouse pointer hovers over it. Just looking at the code, I cannot readily see any reason for an error, but sometimes blank cells in a column or row can cause a change in a variable value. This is especially true with a Find statement. Since you are using the find statement while in filter mode, it could be that a found cell is not visible and is causing the problem. But that is just a guess.

I will look into it, there might be a need for me to change the entire code to import from a specific column of one file to a specific column on another based on criteria.

Appreciate you looking into this JLGWhiz.

Originally Posted by smartbuyer

I will look into it, there might be a need for me to change the entire code to import from a specific column of one file to a specific column on another based on criteria.

Appreciate you looking into this JLGWhiz.

Glad to assist, hope you find the problem.
Regards, JLG

t

Originally Posted by JLGWhiz

The images did not come through, but maybe this will help. In this:

line of code, change to

Set found = wsProc.Columns(1).Find(cel.Value, lookat:=xlWhole)

tried updating the line of code as above, still getting the error.