• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum data using VBA - YTD , MTD & YR16 & YR17

trprasad78

Member
Hi experts

I am looking for vba to sum the value based on YTD, MTD ,YR16& YR17.

full year (financial year ) from 1st April 2016 31st March 2017

YTD = 1st April 2016 till last month
MTD = Current Month till date
YR16 = 1st April 2016 31st March 2017

Month start from "E17"

Start month and end month vary file to file. so i dont know how to handle that.

Result need/output = Sample1.xlsx [Report1 & Report2]

output Report1
Name = Folder name
Customer =AAR!B4
Project=AAR!B1
Costcenter=AAR!B2
Revenue = YTD
Total Cost=YTD
Revenue=MTD (for the month)
Total Cost=MTD (for the month)
Revenue=MONTHS - (LESS) YTD & MTD

OUTPUT Report2

Name = Folder name
Customer =AAR!B4
Project=AAR!B1
Costcenter=AAR!B2
Revenue&Total cost FY17 = 1st April 2016 31st March 2017
Revenue&Total cost FY18 = 1st April 2017 31st March 2018
Revenue&Total cost FY19 = 1st April 2018 31st March 2019

we have some 50 files like this all data sum and get in to report1 & report2

if you able to help me it will be great, please do the needful
 

Attachments

  • Sample2.xlsx
    19.1 KB · Views: 1
  • Sample1.xlsx
    20.8 KB · Views: 5
my experiment i stuck in sum of revenue

Code:
Dim path As String
Dim RPT As String




path = ActiveWorkbook.path

RPT = Application.WorksheetFunction.Rept(" ", 100)


'Trim(Right(Replace(path, "\", Rept(" ", "100"), 100)))

Range("A4").Value = Trim(Right(Replace(path, "\", RPT), 100))
Range("B4").Value = Worksheets("AAR").Range("b4").Value
Range("c4").Value = Worksheets("AAR").Range("b1").Value
Range("d4").Value = Worksheets("AAR").Range("b2").Value

With Sheets("AAR")
    Set FindRow = .Range("A:A").Find(What:="Revenue", LookIn:=xlValues)
End With
 
Are You sure ... that Your sample results are correct?
Would You check You formulas?

MTD = Current Month till date
... now, it's Jan-17 ... MTD is Jan-17 till 14th Jan 2017 or what?

and how to get:
Revenue&Total cost FY17, Revenue&Total cost FY18, Revenue&Total cost FY19
if there is no data...

and where do You need VBA?

Sample2.xlsx is for ... ?
 
Are You sure ... that Your sample results are correct?
Would You check You formulas?

MTD = Current Month till date
... now, it's Jan-17 ... MTD is Jan-17 till 14th Jan 2017 or what?

and how to get:
Revenue&Total cost FY17, Revenue&Total cost FY18, Revenue&Total cost FY19
if there is no data...

and where do You need VBA?

Sample2.xlsx is for ... ?

Thank you for your question,

MTD just it can take the current month is fine, not necessarily 14th Jan. Because that will be handled while do data entry in "AAR" sheet

Data will be their till the project complet. It mean project value we enter, if month is end jan-17 like that then rest of the FY18&FY19 should be zero.
Out of 50 excel start month will be vary. Based on when project will start and end.
50 sheets are in 50 different folders and subwoofers.

Actually Report 1&Report 2 sheets are in separate file name called output file, where VBA as to run and capture all the data from excel file located in folder and subfolders.
 
I have attached output file.

But if we keep out put file separate is it possible to get the folder name of the file is located ?

or if we maintain one sheet in each file where all summary will capture and then we copy all summary in to one comman file ?

Please suggest.
 

Attachments

  • Output.xlsx
    15.3 KB · Views: 1
@trprasad78
question ... then waiting for an answer
Do You mean that Your 'sample results' are correct?
If so,
then I won't start to guess nor test ...
I could only offer 'macro'
how to COPY those 'sample results' to one output-file.
 
Back
Top