Excel With VBA

How To Use Excel VBA To Loop Through A Range And Copy Conditionally

In this post, we will loop a list of bank account transactions, and copy them programmatically to another sheet based on their date.

Here is an image of the sheet:

In the account ledger, we are going to loop the rows in the “Full Bank Statement” sheet, and extract only the rows that fall within the dates shown in “M2” and “N2”.

If the row data does fall within those date parameters, we will copy and paste the row data into the “July” sheet.

Here is the code:

Sub Button1_Click()
    '2019 - http://vbastring.com
    
    ProcessCells
    
    MsgBox "Complete"
    
    
End Sub


Sub ProcessCells()
    '2019 - http://vbastring.com
    
    Dim intLastRow As Integer
    Dim intRow As Integer
    
    Dim dteStart As Date
    Dim dteEnd As Date
    
    Dim dteToEvaluate As Date
    
    dteStart = Sheets("Full Bank Statement").Range("M2")
    dteEnd = Sheets("Full Bank Statement").Range("N2")
    
    Dim intDestRow As Integer
    
    intDestRow = 2
    intLastRow = 14000
    
    
    For intRow = 2 To intLastRow
        dteToEvaluate = Sheets("Full Bank Statement").Range("A" & intRow)
        
        If dteToEvaluate >= dteStart And dteToEvaluate <= dteEnd Then
            
            'copy it
        
            Sheets("Full Bank Statement").Range(Sheets("Full Bank Statement").Cells(intRow, 1), Sheets("Full Bank Statement").Cells(intRow, 10)).Copy
            
            Sheets("July").Select
            Sheets("July").Range("A" & intDestRow).Select
            Sheets("July").Paste
            
            intDestRow = intDestRow + 1
            
        End If
        
    Next
    
    'set the focus back to the original sheet
    
    Sheets("Full Bank Statement").Select
End Sub

After all the processing is complete, your “July” sheet should look like this:

You can download a “look alike” file here (The file looks different but does the same thing):

Excel VBA To Loop Through A Range And Copy Conditionally.xlsm

Watch how it’s done:

Let me know if you have any questions.

****************************************************

admin