Displaying YTD for each Month Selected

[Fishleg]

Hi,
I would appreciate any help you are able to offer. I am working on a
spreadsheet that should displays YTD figures for each month selected.
I have already got help with displaying YTD figures based on Year
Beginning from January and ending December. I now need to be able to
display YTD when year begins in any other month than January e.g.
year begins in April and ends in March.

Thanks in advance for any help you are able to offer.

 

[Joel]

If this is not right, post your previous formula and I will modifiy it.

StartDate = DateValue("4/1/07")
EndDate = DateValue("2/1/08")

YTD = EndDate - StartDate

 

[Fishleg]

Thanks Joel,
I do not have a formula but the codes listed below. I am quite new to
this as well.


Private Sub getmonth()
Application.ScreenUpdating = False

mymonth = Month(DateValue(Range("month") & ",1,2007")) + 1
'MsgBox mymonth
'copy monthly data
With Sheets("Data")
.Range(.Cells(53, mymonth), .Cells(58, mymonth)).Copy
Sheets("Shell").Range("c10")
.Range(.Cells(24, mymonth), .Cells(29, mymonth)).Copy
Sheets("Shell").Range("d10")
.Range(.Cells(2, mymonth), .Cells(7, mymonth)).Copy
Sheets("Shell").Range("e10")
.Range(.Cells(46, mymonth), .Cells(51, mymonth)).Copy
Sheets("Shell").Range("f10")

'Get YTD total
For i = 10 To 15

Sheets("Shell").Cells(i, "h") = Application.Sum(.Range(.Cells(i +
43, 2), .Cells(i + 43, mymonth)))
Sheets("Shell").Cells(i, "k") = Application.Sum(.Range(.Cells(i -
8, 2), .Cells(i - 8, mymonth)))
Sheets("Shell").Cells(i, "i") = Application.Sum(.Range(.Cells(i +
14, 2), .Cells(i + 14, mymonth)))
Sheets("Shell").Cells(i, "m") = Application.Sum(.Range(.Cells(i +
36, 2), .Cells(i + 36, mymonth)))
Next i
End With
'Range("H8") = "YTD since January"
Application.ScreenUpdating = True
End Sub

There are two spreadsheets one called data and the other called
shell. Users select month from the drop down list and click on a
button to display the ytd for month selected in the shell sheet.
Figures are copied from the data sheet. This however only works for
Jan -Dec. I need to get code amended so YTD is displayed for any month
choosen as the first month of the year.

 

[Joel]

See if this helps. I hard coded the StartMonth at 5 but you can change this
line as needed. I also added MonthName() function to the cell H8 (this line
was commented out). I also made some editorial type changes to make the code
easier to read and understand.


Private Sub getmonth()
Application.ScreenUpdating = False

StartMonth = 5 'If start in May
mymonth = Month(DateValue(Range("month") & ",1,2007"))

If StartMonth <= mymonth Then
MonthColumn = (mymonth - StartMonth) + 2
Else
MonthColumn = (12 + mymonth - StartMonth) + 2
End If
'MsgBox MonthColumn
'copy monthly data
With Sheets("Data")
.Range(.Cells(53, MonthColumn), .Cells(58, MonthColumn)).Copy _
Sheets("Shell").Range("c10")
.Range(.Cells(24, MonthColumn), .Cells(29, MonthColumn)).Copy _
Sheets("Shell").Range("d10")
.Range(.Cells(2, MonthColumn), .Cells(7, MonthColumn)).Copy _
Sheets("Shell").Range("e10")
.Range(.Cells(46, MonthColumn), .Cells(51, MonthColumn)).Copy _
Sheets("Shell").Range("f10")

'Get YTD total
For RowCount = 10 To 15

Sheets("Shell").Cells(RowCount, "h") = Application.Sum( _
.Range(.Cells(RowCount + 43, 2), _
.Cells(RowCount + 43, MonthColumn)))
Sheets("Shell").Cells(RowCount, "k") = Application.Sum( _
.Range(.Cells(RowCount - 8, 2), _
.Cells(RowCount - 8, MonthColumn)))
Sheets("Shell").Cells(RowCount, "i") = Application.Sum( _
.Range(.Cells(RowCount + 14, 2), _
.Cells(RowCount + 14, MonthColumn)))
Sheets("Shell").Cells(RowCount, "m") = Application.Sum( _
.Range(.Cells(RowCount + 36, 2), _
.Cells(RowCount + 36, MonthColumn)))
Next RowCount
End With

Range("H8") = "YTD since " & MonthName(StartMonth)
Application.ScreenUpdating = True
End Sub

 

[Fishleg]

Joel,
Thanks Joel for your help with this much appreciated. Yes the code
worked! This however would require me to create 12 different workbooks
as the year could begin in any of the 12months of the year which
really isn't what I want to do. I would prefer to be able to select
any month as the first month of the year and then calculate the ytd in
the same workbook.

Thanks in advance for your suggestion and help.

 

[Joel]

I don't know exactly what your worksheets look like nor do I know where each
months data is located.. Most times when I'v seen accounting spreadsheets
for physical years column 2 header would contain the first month such as May.
Each column would be the follwing month with the 13th column being April
(when May is the first month). The macro can look at the month name in cell
B2 and automatically calculate the correct

I can make any changes you like if you give me the details. Sorry that I
didn't get it exactly right. I assumed that Range("month") was the current
month. It sound like this was the start month. If so the code would lokk
like this


from
StartMonth = 5 'If start in May
mymonth = Month(DateValue(Range("month") & ",1,2007"))
to:
StartMonth = Month(DateValue(Range("month") & ",1,2007"))
mymonth = Month(date)

The rest of the code would be the same.

 

[Fishleg]

I don't know exactly what your worksheets look like nor do I know where each
months data is located..  Most times when I'v seen accounting spreadsheets  
for physical years column 2 header would contain the first month such as May.
 Each column would be the follwing month with the 13th column being April
(when May is the first month).  The macro can look at the month name in cell
B2 and automatically calculate the correct

I can make any changes you like if you give me the details.  Sorry that I
didn't get it exactly right.  I assumed that Range("month") was the current
month.  It sound like this was the start month.  If so the code would lokk
like this

from
StartMonth = 5  'If start in May
mymonth = Month(DateValue(Range("month") & ",1,2007"))
to:
StartMonth = Month(DateValue(Range("month") & ",1,2007"))
mymonth = Month(date)

The rest of the code would be the same.







- Show quoted text -

 

[Fishleg]

Joel,
Many thanks again. Yes you are right you would understand this
spreadsheet better if you have a look at it. Would you mind me
emailing it to you. I have tried this new code but it does not do what
I want the spreadsheet to do.