Using VBA to paste rows into an empty row

[ZeroSugar]

I've been hobbling over this for couple hours now with different varieties with no luck, but with the exact result funnily enough.

Here's a snippet of my latest attempt. It's a bit more involved than copy/paste overall, but this is the part I'm having trouble with.

For i = 1 To lastRow
' Check if the name in column E is "USED"
If sourceSheet.Cells(i, "E").Value = "USED" Then
' Copy the row and paste it as values in the next empty row in the target sheet
sourceSheet.Rows(i).Copy
targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next i


I can create the loop to copy any row(s) with a specific word from a specific sheet, but my intention that it continuously pastes each row on a different sheet starting with the next empty row. Currently what's happening is that it pastes over the exact same row it just did. I feel like I'm getting real brain fog after spending all this time, so any pointers are appreciated. Thank you!

 

[Joe4]

Welcome to the Board!

Have you placed this code in a General Module, or one of the sheet modules?
Can you show us the rest of the code, so we can see how "lastRow", "sourceSheet", and "targetSheet" are calculated/set?
See here for how to post your code: How to Post Your VBA Code

 

[ZeroSugar]

Yes I've placed in the general modules. I don't know if I can edit my OP, so I'm pasting the VBA in this response. Thank you!

Sub Cut_To_DISCARD_REPORT()

    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the source and target sheets
    Set sourceSheet = ThisWorkbook.Sheets("LIST")
    Set targetSheet = ThisWorkbook.Sheets("TO DISCARD")
    
    ' Find the last row in the source sheet
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row in the source sheet
    For i = 1 To lastRow
        ' Check if the name in column E is "COMPLETED"
        If sourceSheet.Cells(i, "E").Value = "COMPLETED" Then
            ' Copy the row and paste it as values in the next empty row in the target sheet
            sourceSheet.Rows(i).Copy
            targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
    Next i
    
    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub

 

[Joe4]

I just did a super simple example, and your code seemed to work fine for me.

This is data I set-up for my "LIST" sheet:

On the "TO DISCARD" sheet, I only started with the header row.
According to your logic, 3 rows should get copied over.
This is what the "TO DISCARD" sheet looks like when done:

So it seems to work as expected.
Does it not do this for you?
If so, please show us some data examples.

 

[ZeroSugar]

I'm actually shook that it does work when I re-created your sample. I didn't think to try it out on a basic sample set.

When I watch it run on the file I'm working on, I can see it's parsing each row correctly with the word "Completed" to copy it. I inserted a brand new sheet and adjusted the code accordingly as test. Yet the VBA is still pasting to the exact same row for some reason...

I plan to upload the file once I do some basic cleanup. Not exactly top secret file, but just making sure. Thanks!

 

[Joe4]

I'm actually shook that it does work when I re-created your sample. I didn't think to try it out on a basic sample set.

When I watch it run on the file I'm working on, I can see it's parsing each row correctly with the word "Completed" to copy it. I inserted a brand new sheet and adjusted the code accordingly as test. Yet the VBA is still pasting to the exact same row for some reason...

I plan to upload the file once I do some basic cleanup. Not exactly top secret file, but just making sure. Thanks!

Yes, if you can provide a problem file, I would be glad to try running the code against it.

Note that you cannot upload files to this site. But you can upload the file to any file sharing site (or OneDrive or Google Drive), and provide a link to it here in this post.

 

[ZeroSugar]

First, I appreciate your input and validation for me! I found out that column A was the culprit. We've been leaving that column with 3-5 inputs, so I thought I just delete it anyways to simply our list among other column deletions. Ran the code and it worked...

I don't recall what exactly was in that column that may have bugged out Excel since it was just text letters, but it's past me now. Thanks a million!

 

[Joe4]

You are welcome.
Glad it is all working for you now.