Copy/paste a row from within a loop, before that loop moves on to next cell

[netrixuser]

I have some code working, but I need to add some lines to copy a row of text before I delete that text.

I am getting a mismatch error and assume its something to do with the variable "cel" being set as a range ? but, as with most of my code, I pull bits from the web and get it working without truly understanding the basics, my bad I know.

To explain further, In my Workbook:
The "Scratch Sheet" tab has data imported (temporarily) from a csv file, this csv file contains a list or alerts we receive from a vendor.
Some of the alerts that were previously "Existing" are now set to CLOSED in the csv file, so below, the code looks for alerts with CLOSED in Scratch Sheet column A, then finds the corresponding alert in the "Current Alerts" tab (using a unique Alert ID in column B) and then deletes the entire row.

My goal is to copy the entire row of each alert to a separate tab named "Closed Alerts" before the entire row is deleted. This Closed Alerts tab should maintain an ongoing list pf all closed alerts, ie append each Closed Alert to the bottom of the list.

Working Code that finds the closed alerts and deletes them

Sub RemClosed() 

Dim statRng As Range, cel As Range, fndAlert As Range, lrow As Integer

    With Workbooks("Cloud9 Alerts Master List.xlsm").Sheets("Scratch Sheet")    
        Set statRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))    'set range to Column A
    End With
    
    For Each cel In statRng
        If cel.Value = "CLOSED" Then 
            With Sheets("Current Alerts")
                Set fndAlert = .Range("B:B").Find(cel.Offset(, 1).Value, , xlValues, xlWhole)
                    If Not fndAlert Is Nothing Then
                        fndAlert.EntireRow.Delete
                    End If
            End With

    End If
Next cel

What I have working at the moment is to copy a cell that is two across from the Variable called "cel" using Offset. This is ok 'ish but I would prefer the whole row to be copied.
Its not actually "copying" rather placing the value of one cell into another cell.

Dim statRng As Range, cel As Range, fndAlert As Range, lrow As Integer
'Dim copyrow As Range

    With Workbooks("Cloud9 Alerts Master List.xlsm").Sheets("Scratch Sheet") 
        Set statRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    For Each cel In statRng
        If cel.Value = "CLOSED" Then
  
'Here is the bit I have working
                lrow = Worksheets("Closed Alerts").Cells(Rows.Count, 1).End(xlUp).Row + 1  
                Sheets("Closed Alerts").Range("A" & lrow).Value = cel.Offset(0, 2).Value

        With Sheets("Current Alerts")
            Set fndAlert = .Range("B:B").Find(cel.Offset(, 1).Value, , xlValues, xlWhole)   'look for the closed alerts in Current Alert Sheet and delete the entirerow
                If Not fndAlert Is Nothing Then
                    fndAlert.EntireRow.Delete
                End If
        End With

    End If
Next cel

I've tried to be a bit more adventurous below but get the mismatch error with this attempt:
[I know I should put all the Dim statements at the top]

Dim statRng As Range, cel As Range, fndAlert As Range, lrow As Integer
'Dim copyrow As Range

    With Workbooks("Cloud9 Alerts Master List.xlsm").Sheets("Scratch Sheet")
        Set statRng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) 
    End With
    
    For Each cel In statRng
        If cel.Value = "CLOSED" Then



Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Scratch Sheet")
set ws2 = ThisWorkbook.Sheets("Closed Alerts")

    
                lrow = Worksheets("Closed Alerts").Cells(Rows.Count, 1).End(xlUp).Row + 1   'find last row in Closed Alert sheet
                ws1.Rows.cel.EntireRow.Copy ws2.Range("A" & lrow)
       
        With Sheets("Current Alerts")
            Set fndAlert = .Range("B:B").Find(cel.Offset(, 1).Value, , xlValues, xlWhole)   'look for the closed alerts in Current Alert Sheet and delete the entirerow
                If Not fndAlert Is Nothing Then
                    fndAlert.EntireRow.Delete
                End If
        End With

    End If
Next cel

as always, any help given will be most appreciated.

Regards
Netrix

 

[Joe4]

Which line of code is giving you the error (usually, if you click the "Debug" button, it will show you).

Is it this line here?

ws1.Rows.cel.EntireRow.Copy ws2.Range("A" & lrow)

If so, try changing it like this:

ws1.cel.EntireRow.Copy ws2.Range("A" & lrow)

 

[netrixuser]

Which line of code is giving you the error (usually, if you click the "Debug" button, it will show you).

Is it this line here?

ws1.Rows.cel.EntireRow.Copy ws2.Range("A" & lrow)

If so, try changing it like this:

ws1.cel.EntireRow.Copy ws2.Range("A" & lrow)

Thank you for the reply Joe4 - your assumption on which line of code gave me trouble was correct.

I tried your suggestion but got a compile error:

"Method or data member not found" - with the .cel highlighted in your suggested line of code.

 

[Joe4]

Thank you for the reply Joe4 - your assumption on which line of code gave me trouble was correct.

I tried your suggestion but got a compile error:

"Method or data member not found" - with the .cel highlighted in your suggested line of code.

OK, I think because cel is a range variable, and it has already been set in your code, you don't need a sheet reference before it, as it has already been "baked in" to that.

So try just:

cel.EntireRow.Copy ws2.Range("A" & lrow)

 

[netrixuser]

OK, I think because cel is a range variable, and it has already been set in your code, you don't need a sheet reference before it, as it has already been "baked in" to that.

So try just:

cel.EntireRow.Copy ws2.Range("A" & lrow)

You're a star !! Thanks Joe4, all sorted

 

[Joe4]

You are welcome.
Glad I was able to help!