Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying YTD for each Month Selected
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying YTD for each Month Selected
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying YTD for each Month Selected
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 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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying YTD for each Month Selected
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 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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying YTD for each Month Selected
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 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 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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying YTD for each Month Selected
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 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 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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying YTD for each Month Selected
On Dec 28, 10:22*pm, Joel 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 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 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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying YTD for each Month Selected
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 wrote: On Dec 28, 10:22*pm, Joel 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 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 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying month or year as text | Excel Worksheet Functions | |||
Need help displaying odd numbered month | Excel Worksheet Functions | |||
displaying a comment only when the cell is selected | Excel Discussion (Misc queries) | |||
displaying a comment only when the cell is selected | Excel Discussion (Misc queries) | |||
Displaying a selected value from a combo box | Excel Programming |