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.

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" wrote:

> 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.
>

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.

On Dec 26, 3:32*pm, Joel <J...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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.- Hide quoted text -
>
> - Show quoted text -

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" wrote:

>
> 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.
>
> On Dec 26, 3:32 pm, Joel <J...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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.- Hide quoted text -
> >
> > - Show quoted text -
>
>

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.

On Dec 28, 3:05*pm, Joel <J...@discussions.microsoft.com> wrote:
> 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 thecode
> 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" wrote:
>
> > 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.
>
> > On Dec 26, 3:32 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > 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" wrote:
> > > > 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.- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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" wrote:

>
> 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.
>
> On Dec 28, 3:05 pm, Joel <J...@discussions.microsoft.com> wrote:
> > 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" wrote:
> >
> > > 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.
> >
> > > On Dec 26, 3:32 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > > 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" wrote:
> > > > > 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.- Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
>

On Dec 28, 10:22*pm, Joel <J...@discussions.microsoft.com> wrote:
> 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" wrote:
>
> > 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.
>
> > On Dec 28, 3:05 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > 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 makethe 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" wrote:
>
> > > > 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.
>
> > > > On Dec 26, 3:32 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > > > 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" wrote:
> > > > > > 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.- Hide quoted text -
>
> > > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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.




On Dec 29, 10:11*pm, Fishleg <laba.a...@yahoo.com> wrote:
> On Dec 28, 10:22*pm, Joel <J...@discussions.microsoft.com> wrote:
>
>
>
> > 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 asMay.
> > *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" wrote:
>
> > > 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.
>
> > > On Dec 28, 3:05 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > > 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" wrote:
>
> > > > > Thanks Joel,
> > > > > I do not have a formula but the codes listed below. I am quite newto
> > > > > 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.
>
> > > > > On Dec 26, 3:32 pm, Joel <J...@discussions.microsoft.com> wrote:
> > > > > > 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" wrote:
> > > > > > > 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.- Hide quoted text -
>
> > > > > > - Show quoted text -- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -