VBA to Copy and Paste Rows if Condition is Met - New Rows Only

[APoncharoensub]

Hello,

I did a bit of searching through the forums and I found some solutions, but not quite exactly what I was looking for as each query seems to be unique to that person or the OP was not explaining their needs properly.

I am a complete novice when it comes to Macro and have only started using them today to complete a task my boss assigned me.

Basically, we would like to be able to have a row copied from sheet to another when a condition is met automatically. While I have seen actions that will copy all data from one sheet to another based on a condition, I have yet to find the right coding where it will only copy a brand new row that I just entered.

A YouTube tutorial was able to outline the following code with a command button:

Private Sub CommandButton2_Click()a = Worksheets("Marketing - Data").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a


    If Worksheets("Operations - Data").Cells(i, 1).Value = "American Express" Then


    Worksheets("Marketing - Data").Rows(i).Copy
    Worksheets("Sheet1").Activate
    b = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Sheet1").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Marketing - Data").Activate


    End If
Next


Application.CutCopyMode = False


ThisWorkbook.Worksheets("Marketing - Data").Cells(1, 1).Select
End Sub

To be more specific, I have a tab labeled "Operations - Data" where I input expenses, and I label each expense with a payment type (Column A) between American Express, Visa, Check, etc. Based on the payment type (condition of Column A in "Operations - Data" sheet), I would like for it to copy the new row I just entered into another tab, in this case, currently marked "Sheet1." As you can already tell, the code above copies all data labeled "American Express" in column A every time I click the Command button. But I would like for it to only copy the new row from "Operations - Data" to "Sheet1."

Apologies if this has already been answered and I just didn't understand what I was reading. If that is the case, can you please link me to the thread that has the answer?

Thanks in advance.

 

[Logit]

.
Presently the macro refers to Sheets("Sheet1") as the destination to paste to. Is Sheet1 the same as Monthly AMEX Statements \ Sheet12 ?

 

[Logit]

.
I put this together presuming Sheet12 is the equivalent of Sheet1 previously mentioned .

Disregard any previous macros. The macros below paste into a MODULE. Place a command button on the "Monthly AMEX Statements" sheet.
After you have entered all debits in the other sheets, go to "Monthly AMEX Statements" sheet and click the button.

Download sample workbook : https://www.amazon.com/clouddrive/share/Kgq0627yxgnMMzMJZNM2AthimDBv6l1ddPRCelfvnPq

Option Explicit


Sub CpyAMERXPRS()
Dim Rws As Long, Rng As Range, ws As Worksheet, sh As Worksheet, c As Range, x As Integer
    Set ws = Worksheets("Monthly AMEX Statements")  'specify sheet name here to paste to
    x = 2   'begins pasting in Sheet RFQ on row 2
Application.ScreenUpdating = False
    
    Sheet12.Range("A2:H5000").Value = ""
    
    For Each sh In Sheets
        If sh.Name <> ws.Name Then
            With sh
                For Each c In .Range("A2:H5000")            '<--- look at first 500 rows below row 1
                    If c.Value = "American Express" Then    'searches for "American Express" cells
                        c.EntireRow.Copy
                        ws.Range("A" & x).PasteSpecial Paste:=xlValues
                         
                        x = x + 1
        End If
                Next c
            End With
        End If
    Next sh
    
    srtDates
    
    GetSum
    
ws.Range("A1").Select
Application.ScreenUpdating = True


End Sub


Sub srtDates()
    Range("B2:B5000").Select
    ActiveWorkbook.Worksheets("Monthly AMEX Statements").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Monthly AMEX Statements").Sort.SortFields.Add Key _
        :=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Monthly AMEX Statements").Sort
        .SetRange Range("A2:H5000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub




Sub GetSum()
Dim lastrow As Long
lastrow = ThisWorkbook.Sheets("Monthly AMEX Statements").Cells(Rows.Count, 4).End(xlUp).Row
ThisWorkbook.Sheets("Monthly AMEX Statements").Range("D" & lastrow + 2) = "TOTAL : "
ThisWorkbook.Sheets("Monthly AMEX Statements").Range("D" & lastrow + 2).HorizontalAlignment = xlHAlignRight
ThisWorkbook.Sheets("Monthly AMEX Statements").Range("D" & lastrow + 2).Font.Bold = True
ThisWorkbook.Sheets("Monthly AMEX Statements").Range("E" & lastrow + 2) = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("Monthly AMEX Statements").Range("D2:D" & lastrow))
ThisWorkbook.Sheets("Monthly AMEX Statements").Range("E" & lastrow + 2).Font.Bold = True
End Sub

 

[APoncharoensub]

Hi Logit,

Thanks a lot for this. I didn't get a email notification for this. Apologies for the delay.

Your sample workbook works PERFECTLY! However, when I try to create my own command button using your macro, it doesn't sort the dates. I'm not sure if it's something I'm doing wrong.

I create the Module, created the button, and assigned the CpyAMERXPRS and run. While it collects all the new data entered in the other sheets, it will not sort the dates as in your sample workbook.

Thank you again for all your help.

 

[Logit]

.
The macro auto-sorts on the sheet "Monthly AMEX Statements". Did you want it to auto sort on
other sheets as well ?

 

[APoncharoensub]

It looks like it's not auto-sorting on the sheet "Monthly AMEX Statements" at all. When I add your macro, it works, however, all the new data lines appear at the top of the sheet "Monthly AMEX Statement."

 

[Logit]

Did you copy the macros from my download example and paste them to another workbook ? If so, could you post
your version of the workbook to a cloud site for download so I can look at you code ?

The workbook download, as is, sorts on the DATE column and functions correctly here.

 

[APoncharoensub]

I copied the macro directly from the message board as well as imported your macro from your Sample workbook. While it sorts the DATE on your Sample workbook, when copied or imported to a different workbook, it will not sort the dates.

Please find the link to my Sample workbook with your macro imported: https://drive.google.com/open?id=1-2ebvJ62--PskeZ8xuih5b2MOsEf9kJD

Thanks for all your help.

 

[Logit]

.
Your workbook was missing this macro :

 Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("B1").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

The macro is to be pasted in the SHEET12(Monthly AMEX Statements) sheet module. You presently have 3 other macros located there as well.

As the macro name implies, anytime a change in made in the second column (B2 and down), the macro auto-sorts all lines of the data based
on date.

I noticed in your posted example workbook some of your trial data has a date format of 3/10/2019 and the majority of the data uses a
date format of 03/10/2019. You will either need to use the same date input for all records or add code that will insure all dates
are displayed the same.

 

[APoncharoensub]

Thanks for clarifying that. For some odd reason, despite exporting the macro from the Sample workbook, it didn't include that date-sorting macro.

Yes, I've been having a lot of trouble formatting the dates. For some odd reason, whenever I input a date, it changes the date to this number: 43528. I've formatted the numbers in the DATE column to "Short Date" multiple times, but it always changes to that strange number. Therefore, the date formatting has been very inconsistent.

Thanks for all your help.

 

[Logit]

.
You are welcome.