Report Macros Issue. Help!

[DiHa9]

Hi all,

I am very new to VBA and i was trying to create some simple transaction reports.



I created the Macro to first sort the data from the big table on the left into income and Expenses and sum the up if there are more than one line with the same Expense/Income type. And this part of the code works fine as you can see from the picture above. (This is Sheet9)

What i want to do next is take the results from this two smaller tables and display the into a type of Income & Expense report on Sheet1.



The issue is as you can see that the report displays all the individual income inputs but when it comes to the expenses it displays all the expenses except the last one.

The code i have written is as follows:

>>> use code - tags <<<

Sub Report3()
'Income & Expense Report
Dim LastTransRow As Long, LastIncRow As Long, LastExpRow As Long, RepRow As Long
With Sheet9
LastTransRow = .Range("A99999").End(xlUp).Row
'Income Result
.Range("C3:D" & LastTransRow).AdvancedFilter xlFilterCopy, CriteriaRange:=.Range("J2:J3"), CopyToRange:=.Range("M2"), Unique:=True
LastIncRow = .Range("M99999").End(xlUp).Row
'Expense Results
.Range("C3:D" & LastTransRow).AdvancedFilter xlFilterCopy, CriteriaRange:=.Range("K2:K3"), CopyToRange:=.Range("P2"), Unique:=True
LastExpRow = .Range("P99999").End(xlUp).Row
End With
With Sheet1
.Range("AD4").Value = "ACCOUNT"
.Range("AE4").Value = "AMOUNT"
.Range("AD5").Value = "INCOME"
.Range("AD6:AE" & LastIncRow + 3).Value = Sheet9.Range("M3:N" & LastIncRow).Value
RepRow = LastIncRow + 4 'Report Row
.Range("AD" & RepRow).Value = "TOTAL INCOME"
.Range("AE" & RepRow).Value = "=Sum(AE6:AE" & RepRow - 1 & ")"
.Range("AD" & RepRow + 1).Value = "EXPENSES"
.Range("AD" & RepRow + 2 & ":AE" & RepRow + LastExpRow - 1).Value = Sheet9.Range("P3:Q" & LastExpRow).Value 'Expense Data
.Range("AD" & RepRow + LastExpRow - 1).Value = "TOTAL EXPENSES"
.Range("AE" & RepRow + LastExpRow - 1).Value = "=Sum(AE" & RepRow + 2 & ":AE" & RepRow + LastExpRow - 2 & ")"
.Range("AD" & RepRow + LastExpRow).Value = "TOTAL PROFIT"
.Range("AE" & RepRow + LastExpRow).Value = .Range("AE" & RepRow).Value - .Range("AE" & RepRow + LastExpRow - 1).Value
Sheet1.Range("AE5:AE" & RepRow + LastExpRow).NumberFormat = "€ 0.00"
End With
End Sub

I have stated in the bold line to copy all inputs from P3:Q & LastExpRow but for some reason in doesnt work for the expenses.

Can some one help and point out as to what I am doing wrong here ?

Thank you very much in Advance!

 

[Debaser]

Cross-posted: https://forum.ozgrid.com/forum/index.php?thread/1233722-report-macro-issue/

 

[vletm]

DiHa9​

As You've read ...

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

from

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[p45cal]

Pivot table with timeline?
See attached.

 

[DiHa9]

My apologies for the cross-posting. I am new to all this. This is my first time posting an issue.

Here is the answer from the other post I got :

You overwrite the last row with these lines:

.Range("AD" & RepRow + LastExpRow - 1).Value = "TOTAL EXPENSES"
.Range("AE" & RepRow + LastExpRow - 1).Value = "=Sum(AE" & RepRow + 2 & ":AE" & RepRow + LastExpRow - 2 & ")"

Again, my apologies for any inconvenience