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.
****************************************************
|