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]

.
Sorry .. there was too much going wrong with the macro you posted.

Try this one :

Option Explicit


Sub Copy_n_Paste()
On Error Resume Next


    Dim srchtrm As String
    Dim rng As Range, destRow As Long
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim c As Range
    Dim i As Integer
    Dim Today As Date
    
    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    Set shtSrc = Sheets("Operations - Data")    'source sheet
    Set shtDest = Sheets("Marketing - Data")    'destination sheet
    destRow = 2 'start copying to this row


    'don't scan the entire column...
    Set rng = Application.Intersect(shtSrc.Range("A:A"), shtSrc.UsedRange)


    For Each c In rng.Cells
        If c.Value = "American Express" Then
            
            c.EntireRow.Copy shtDest.Cells(destRow, 1)
          
            destRow = destRow + 1


        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
        
    Application.CutCopyMode = False
    Sheets("Operations - Data").Range("A1").Select


End Sub

 

[APoncharoensub]

Thanks so much for this.

Apologies, the macro was from two different Command Buttons. I only intended to show one as I was going to copy data from the "Operations - Data" sheet and eventually start working to pull data from other sheets once I learned how to do that one first.

In this instance, would I simply place this macro for the entire sheet?

As I said, I am 100% a novice at this. So come instructions on how to implement this code would also be helpful.

 

[Logit]

.
No problem on the macro ... you have the desire to learn and that is great !
Writing code is like building a house. There are so many different ways to do it but in the end
you still have a house.

Anyway, right click any of the sheet tabs and click on SHOW CODE.

If you haven't already created a MODULE, click on the menu bar up top INSERT. Then click on MODULE.

You will paste the macro into the module window on the right side of the screen. It will be the larger white area.

Then go back to the SHEET where you want the COMMAND BUTTON placed ... click on the DEVELOPER MENU up top,
and then click on INSERT. You can use a userform button on the sheet by clicking on the small button icon in the upper
left corner of the drop down. Then move your cursor where you want the button and left click the sheet.

The button will be placed in that location and another small window will open. Select the macro name from the list
named : Copy_n_Paste

You are done. Now, every time you click on the command button, it will run the macro. Make certain you name your two
tabs as you have indicated in your posts.

The macro as written will only search for the term "American Express". If you intend to search for other terms to be copied / paste
we will need to edit the macro.

 

[APoncharoensub]

Hi Logit,

Thanks so much for all your help. I appreciate your patience!

It works perfectly, but it seems that the data is going to the wrong place. The code should be scanning "American Express" on the "Operations - Data" sheet and copying it to "Sheet1"

Should I simply change the following:

[COLOR=#333333]Set shtDest = Sheets("Sheet1") 'destination sheet[/COLOR]

Also, since I will be needing to pull from other sheets at some point, is it possible to add more coding to this macro to pull from multiple sheets or should I be copying the macro for another command button?

 

[Logit]

.
Yes ... change that to :

[COLOR=#333333]Set shtDest = Sheets("Sheet1") 'destination sheet[/COLOR]

Also, you can change the source sheet to :

 Set shtSrc = ActiveSheet

 

[APoncharoensub]

Thanks again!

So I added another source sheet to create the following:

Option Explicit



Sub Copy_n_Paste()
On Error Resume Next




    Dim srchtrm As String
    Dim rng As Range, destRow As Long
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim c As Range
    Dim i As Integer
    Dim Today As Date
    
    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    Set shtSrc = Sheets("Operations - Data")    'source sheet
    Set shtSrc = Sheets("Marketing - Data")    'source sheet
    Set shtDest = Sheets("Sheet1")    'destination sheet
    destRow = 2 'start copying to this row




    'don't scan the entire column...
    Set rng = Application.Intersect(shtSrc.Range("A:A"), shtSrc.UsedRange)




    For Each c In rng.Cells
        If c.Value = "American Express" Then
            
            c.EntireRow.Copy shtDest.Cells(destRow, 1)
          
            destRow = destRow + 1




        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
        
    Application.CutCopyMode = False
    Sheets("Operations - Data").Range("A1").Select




End Sub

Hope that works. For each new sheet that I want to pull from, will I need to create a new separate module or just modify the existing module?

You've been a great help, but I do have one more question. Because of the order of the coding, it copies new rows from "Marketing - Data" above all of the rows from "Operations - Data." Is there any way to add a code to the macro that will automatically sort all existing data by date when copying new rows?

 

[Logit]

.
THe macro you posted in your last message is incorrect. The following is the correct macro :

Option Explicit




Sub Copy_n_Paste()
On Error Resume Next




    Dim srchtrm As String
    Dim rng As Range, destRow As Long
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim c As Range
    Dim i As Integer
    Dim Today As Date
    
    With Application
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    Set shtSrc = ActiveSheet          'source sheet
    Set shtDest = Sheets("Sheet1")     'destination sheet
    destRow = 2 'start copying to this row




    'don't scan the entire column...
    Set rng = Application.Intersect(shtSrc.Range("A:A"), shtSrc.UsedRange)




    For Each c In rng.Cells
        If c.Value = "American Express" Then
            
            c.EntireRow.Copy shtDest.Cells(destRow, 1)
          
            destRow = destRow + 1




        End If
    Next
    
    With Application
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
        
    Application.CutCopyMode = False
    Sheets("Operations - Data").Range("A1").Select
End Sub

You can use this macro for all sheets because it is only looking at the ACTIVESHEET for records to copy. So .. whatever sheet you are looking at, at the time the macro is run, is the
data the macro will deal with.

Your last question opens another door on your project. I am suspecting you will want to pull different data from different sheets and the data will not always be AMERICAN EXPRESS.
Before going any further you should fully describe your workbook ... the overall goal regarding all of the sheets and all of the data.

If we solve one problem now without fully knowing the overall workbook layout and the ultimate goal ... we are going to start walking down dead ends.

Post a sample workbook (it doesn't have to work at this time) that demonstrates how the entire workbook is laid out. Clearly describe what each sheet does ... what you expect the
macro/s to do with that data ... AND ... once the macro/s are used how the data will appear and on what sheet/s will that data appear.

 

[APoncharoensub]

Thanks for your help once again. You're very kind.

I looked at the forum's rules for attaching workbooks, but I wasn't comfortable downloading software on my work computer. I hope it is okay, but I have uploaded the sample workbook to Google Drive for you to view and/or download: https://drive.google.com/file/d/19MyRWXavZruyldfGIRfeSgR0ZvwLRpuW/view?usp=sharing

The workbook is meant to lay out my company's expenses for the year, sorted by DEPT, with each sheet representing a different department: "DEPT1, DEPT2, DEPT3, Marketing - Data, Operations - Data." The objective is that once I enter a new expense as a new row in a department's sheet, the macro should identify the payment type and copy the new expense (row) into another sheet. At this moment, the goal is that with each new expense (row) entered in any department (sheet), the macro will scan and copy the new expense (row) into the sheet marked "Monthly AMEX Statements" and sort it by chronological order by date entered.

In a day-to-day, I was hoping our automated procedure would be:
1) Enter a new expense (row) in a sheet like "Operations - Data," labelled "American Express"
2) Enter another new expense (row) in another department (sheet) like "Marketing - Data," also labelled "American Express"
3) Run the macro to identify "American Express" rows and copy the newly entered expenses (rows) in the sheet marked "Monthly AMEX Statements" and automatically sort in chronological by date entered

Let me know if you have any further questions. I hope I was clear enough. I really appreciate all the time you've devoted to assisting me!

 

[Logit]

.
Got the workbook. Give me some time to review it.