How to Paste a Row to the Next Empty Row on Another Sheet

[jei13jei]

Hello,

I have created file wherein the button will auto transfer the row from Customer tab (sheet1) to Closed tab (sheet2) when the CLOSE sign is indicated in col K (a.k.a auto transfer when criteria is met). It is already doing the functions that I wan it to do, except for 2 last functions:

1: auto-transfer the whole row (row A:K) from Customer tab to Close tab if col K is marked as CLOSE.
2: erase the details in the row when it's already copied to Close tab

*Missing functions:
3: Paste the CLOSE row details from Customer tab to the Next Empty row on Close tab
4: auto-delete/auto-shift Cells UP when the row in Customer tab is empty (optional)

===Existing code===
Private Sub CommandButton1_Click()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("Customer")
Set Target = ActiveWorkbook.Worksheets("Closed")

j = 1

For Each c In Source.Range("K1:K500")
If c = "CLOSE" Then
Source.Rows(c.Row).Copy Target.Rows(j)
Source.Rows(c.Row).ClearContents
End If
Next c

End Sub
=====

 

[vcoolio]

Hello jei13jei,

Try the following code updated to use autofilter and a dynamic Column K:-

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim ws1 As Worksheet

Set ws = Worksheets("Customer")
Set ws1 = Worksheets("Closed")

Application.ScreenUpdating = False

With ws.[A1].CurrentRegion
             .AutoFilter 11, "CLOSE"
             .Offset(1).EntireRow.Copy
             ws1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
             .Offset(1).EntireRow.Delete
             .AutoFilter
End With
             
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[jei13jei]

It does work! Thank you so much

 

[vcoolio]

You're welcome jei13jei. I'm glad that I was able to help.

Cheerio,
vcoolio.