1

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.

  • 1
    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
1

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.

| improve this answer | |
  • 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
3

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.