How to make excel automatically copy specific cells with date on its side

I have a personal transaction log, and I want to make a transaction summary each months, how to make excel copy cells with specified months.

For Example I have transaction log

         A            B
1    01/25/2018    USD 50.3
2    02/01/2018    USD 21.5
3    02/09/2018    USD 25.4
4    02/17/2018    USD 18.9
5    02/24/2018    USD 34.9
6    02/28/2018    USD 70.5
7    03/02/2018    USD 50.5
8    03/04/2018    USD 22.1

Note: The dates are formatted as date in excel, and the amount are formatted as
Accounting cell


How can I make excel automatically copy to other sheets the transaction in February.

• What have you tried already? Is there something specific that is challenging you? Typically, questions should be a result of your own research and specific hurdles you cannot overcome. Please check out How do I ask a good question for pointers. – jrichall Feb 16 '18 at 20:40

You didn't provide the name or index of your worksheets, so I had to use the generic indices of 1 & 2.

The limited information provided, this is the best I can do for you. But it shouldn't be difficult to modify the code to suit what you are wanting to do exactly.

Option Explicit

Sub copyTransactions()

' ws = the worksheet that contains the code to copy
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

'Create a multi-dimensional array that contains your two columns of data
Dim myArr() As Variant
myArr = ws.UsedRange.Columns("A:B").Value

'ws2 = the worksheet you are copying TO
Dim i As Long, ws2 As Worksheet, x As Long
Set ws2 = ThisWorkbook.Worksheets(2)

'Loop the array, and if it matches your month of 2 (Feb) then copy
'the data from ws to ws2
With ws2
For i = 1 To UBound(myArr)
If month(myArr(i, 1)) = 2 Then  ' 2 = February
x = x + 1
.Cells(x, 1) = myArr(i, 1)  ' the ,1 is column A
.Cells(x, 2) = myArr(i, 2)  ' the ,2 is column B
End If
Next
End With

End Sub


What you are doing in a nutshell is taking the columns A + B and placing them into the array myArr(). You will then loop this array in column A and set criteria to match any month that matches your month index of 2 (2 = Feb). If found, you then proceed to copy the array to ws2.

• Sorry I failed to modify your code to suit into my files. Could you help me modify that code to suit into my workbook. It's location and file name is "D:\Muh Rommy\Documents\xls\Budet Management\Sandbox for Research and Development.xlsm". The Sheets contain The Log is "Sheet7" and the sheet where I going to place the log is "Sheet2" – Rommy Soeli Feb 17 '18 at 3:24

While the VB code is great, I'm not sure if that would be the best solution for those not familiar the developer console.

You can make a column to the side for each month, extract the transactions for that month, and then sum the column for the total.

In column C use something like:

=IF(MONTH($A2)=1,$B2,0)

where 1 would be January, 2 February ...
Then repeat for February in column D