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.

 

[APoncharoensub]

I was testing it out at work with another sample spreadsheet with information closer to our data and I was getting this error in the link below:

https://drive.google.com/file/d/1oG2VZSvcduf3Dzr3aP0NJGHPLMPmEdYI/view?usp=sharing

It was copied and pasted as is from the message board as well as exported from your sample workbook. The error continues to persist.

Thanks!

 

[Logit]

.
Look at the macro code ... all of it. Somewhere below the words END SUB is some extraneous comment / s that shouldn't be there.

Without seeing your actual workbook it would be impossible for me to determine where these words are.

 

[APoncharoensub]

I'm copying and pasting as is from the macro you provided in this thread that worked last weekend.

Thanks for your patience.

Here's a link to the Sample work with the error:
https://drive.google.com/file/d/18KGK-7_PIXG1JbPjBhHoYz-JH-GxK3aC/view?usp=sharing

 

[Logit]

.
In Module1, after sub GETSUM, remove the extra statement OPTION EXPLICIT.

Option Explicit should only be at the very top of the code sheet above any macros that might be included therein.

 

[APoncharoensub]

That did the trick!

Thanks for all your help. Sorry for being a pain!

 

[Logit]

Glad to help.