Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
Hi, I wonder if any one can help I have a sheet that has rows of dates in
that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
You'll need a macro. it is pretty easy to write one but I would like you to
post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
Hi, Joel, Sorry about the delay replying the site said the questions option
was not working so I did not relise it had been posted, what I have is a sheet ( I can call it master) in cell a2 is a list of names then in b2 a start date then each cell after that is 56 days after the cell before, what I would like to do is look at all the dates in row 2 and copy any dates that are in january into another sheet (call it sorted dates) in the other sheet cell a2 would be a copy of the name from a2 in the (master) then in b2 (sorted dates) I would like copy any dates from row b2 to z2 that are January 2008 and in c2 Febuary 2009 from the same range in the master sheet and so on to December 2009 both sheets have names listed down to row 12. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
19 June 2008 14 August 2008 09 October 2008 04 December 2008
13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
It looks like Gord answer your question in another posting.
"Barry" wrote: 19 June 2008 14 August 2008 09 October 2008 04 December 2008 13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
Yes I thought it did but the columns are still mixed up which ever way I
tried it, either sort by row or column, it still did not put all Januray in the first column and all the febuary in thr next and so on. "Joel" wrote: It looks like Gord answer your question in another posting. "Barry" wrote: 19 June 2008 14 August 2008 09 October 2008 04 December 2008 13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
I wrote 2 macros. One to create the worksheets. the 2nd to put data into
the worksheets. Sub createsheets() StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = MyYears For MyMonth = 1 To 12 Cells(1, MyMonth + 1) = MonthName(MyMonth) Next MyMonth Next MyYears End Sub Sub MoveDates() With Sheets("Sheet1") RowCount = 1 Do While .Range("A" & RowCount) < "" Employee = .Range("A" & RowCount) ColCount = 2 Do While .Cells(RowCount, ColCount) < "" MyDate = .Cells(RowCount, ColCount) MyYear = Year(MyDate) MyMonth = Month(MyDate) With Sheets(CStr(MyYear)) 'check if employee already exists Set c = .Columns("A").Find(what:=Employee, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Cells(NewRow, MyMonth + 1) = MyDate .Cells(NewRow, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" Else .Cells(c.Row, MyMonth + 1) = MyDate .Cells(c.Row, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" End If End With ColCount = ColCount + 1 Loop RowCount = RowCount + 1 Loop End With End Sub "Barry" wrote: Yes I thought it did but the columns are still mixed up which ever way I tried it, either sort by row or column, it still did not put all Januray in the first column and all the febuary in thr next and so on. "Joel" wrote: It looks like Gord answer your question in another posting. "Barry" wrote: 19 June 2008 14 August 2008 09 October 2008 04 December 2008 13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
Thank you Joel,Just to show my complete ignorance, where do I put the macros?
"Joel" wrote: I wrote 2 macros. One to create the worksheets. the 2nd to put data into the worksheets. Sub createsheets() StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = MyYears For MyMonth = 1 To 12 Cells(1, MyMonth + 1) = MonthName(MyMonth) Next MyMonth Next MyYears End Sub Sub MoveDates() With Sheets("Sheet1") RowCount = 1 Do While .Range("A" & RowCount) < "" Employee = .Range("A" & RowCount) ColCount = 2 Do While .Cells(RowCount, ColCount) < "" MyDate = .Cells(RowCount, ColCount) MyYear = Year(MyDate) MyMonth = Month(MyDate) With Sheets(CStr(MyYear)) 'check if employee already exists Set c = .Columns("A").Find(what:=Employee, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Cells(NewRow, MyMonth + 1) = MyDate .Cells(NewRow, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" Else .Cells(c.Row, MyMonth + 1) = MyDate .Cells(c.Row, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" End If End With ColCount = ColCount + 1 Loop RowCount = RowCount + 1 Loop End With End Sub "Barry" wrote: Yes I thought it did but the columns are still mixed up which ever way I tried it, either sort by row or column, it still did not put all Januray in the first column and all the febuary in thr next and so on. "Joel" wrote: It looks like Gord answer your question in another posting. "Barry" wrote: 19 June 2008 14 August 2008 09 October 2008 04 December 2008 13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
From worksheet type ALT-F11 to get to VBA window
Add a new module from VBA menu Insert Module Paste code into module sheet Type Alt F11 to get back to worksheet. Run macro from menu Tools - Macro - Maros and choose the correct macro. Run the macro to create the sheet first and only run the macro once. It will give you an error if you run it twice since the worksheets already exist. "Barry" wrote: Thank you Joel,Just to show my complete ignorance, where do I put the macros? "Joel" wrote: I wrote 2 macros. One to create the worksheets. the 2nd to put data into the worksheets. Sub createsheets() StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = MyYears For MyMonth = 1 To 12 Cells(1, MyMonth + 1) = MonthName(MyMonth) Next MyMonth Next MyYears End Sub Sub MoveDates() With Sheets("Sheet1") RowCount = 1 Do While .Range("A" & RowCount) < "" Employee = .Range("A" & RowCount) ColCount = 2 Do While .Cells(RowCount, ColCount) < "" MyDate = .Cells(RowCount, ColCount) MyYear = Year(MyDate) MyMonth = Month(MyDate) With Sheets(CStr(MyYear)) 'check if employee already exists Set c = .Columns("A").Find(what:=Employee, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Cells(NewRow, MyMonth + 1) = MyDate .Cells(NewRow, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" Else .Cells(c.Row, MyMonth + 1) = MyDate .Cells(c.Row, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" End If End With ColCount = ColCount + 1 Loop RowCount = RowCount + 1 Loop End With End Sub "Barry" wrote: Yes I thought it did but the columns are still mixed up which ever way I tried it, either sort by row or column, it still did not put all Januray in the first column and all the febuary in thr next and so on. "Joel" wrote: It looks like Gord answer your question in another posting. "Barry" wrote: 19 June 2008 14 August 2008 09 October 2008 04 December 2008 13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
I have loaded as advised and loaded the dates (01/01/09) and (31/12/09) it
has then come up with Run-time error `13`: Type mismatch, I pressed Debug and it highlighted with an arrow For MyYears = StartYear To EndYear. What did I do wrong? "Joel" wrote: From worksheet type ALT-F11 to get to VBA window Add a new module from VBA menu Insert Module Paste code into module sheet Type Alt F11 to get back to worksheet. Run macro from menu Tools - Macro - Maros and choose the correct macro. Run the macro to create the sheet first and only run the macro once. It will give you an error if you run it twice since the worksheets already exist. "Barry" wrote: Thank you Joel,Just to show my complete ignorance, where do I put the macros? "Joel" wrote: I wrote 2 macros. One to create the worksheets. the 2nd to put data into the worksheets. Sub createsheets() StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = MyYears For MyMonth = 1 To 12 Cells(1, MyMonth + 1) = MonthName(MyMonth) Next MyMonth Next MyYears End Sub Sub MoveDates() With Sheets("Sheet1") RowCount = 1 Do While .Range("A" & RowCount) < "" Employee = .Range("A" & RowCount) ColCount = 2 Do While .Cells(RowCount, ColCount) < "" MyDate = .Cells(RowCount, ColCount) MyYear = Year(MyDate) MyMonth = Month(MyDate) With Sheets(CStr(MyYear)) 'check if employee already exists Set c = .Columns("A").Find(what:=Employee, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Cells(NewRow, MyMonth + 1) = MyDate .Cells(NewRow, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" Else .Cells(c.Row, MyMonth + 1) = MyDate .Cells(c.Row, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" End If End With ColCount = ColCount + 1 Loop RowCount = RowCount + 1 Loop End With End Sub "Barry" wrote: Yes I thought it did but the columns are still mixed up which ever way I tried it, either sort by row or column, it still did not put all Januray in the first column and all the febuary in thr next and so on. "Joel" wrote: It looks like Gord answer your question in another posting. "Barry" wrote: 19 June 2008 14 August 2008 09 October 2008 04 December 2008 13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
We must be using slighlty different version of VBA or with different
settings. the problem is the inputbox is returning a string and on my PC it accepted the string and your computer it didn't try this change from StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear to StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = Val(StartYear) To Val(EndYear) "Barry" wrote: I have loaded as advised and loaded the dates (01/01/09) and (31/12/09) it has then come up with Run-time error `13`: Type mismatch, I pressed Debug and it highlighted with an arrow For MyYears = StartYear To EndYear. What did I do wrong? "Joel" wrote: From worksheet type ALT-F11 to get to VBA window Add a new module from VBA menu Insert Module Paste code into module sheet Type Alt F11 to get back to worksheet. Run macro from menu Tools - Macro - Maros and choose the correct macro. Run the macro to create the sheet first and only run the macro once. It will give you an error if you run it twice since the worksheets already exist. "Barry" wrote: Thank you Joel,Just to show my complete ignorance, where do I put the macros? "Joel" wrote: I wrote 2 macros. One to create the worksheets. the 2nd to put data into the worksheets. Sub createsheets() StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = MyYears For MyMonth = 1 To 12 Cells(1, MyMonth + 1) = MonthName(MyMonth) Next MyMonth Next MyYears End Sub Sub MoveDates() With Sheets("Sheet1") RowCount = 1 Do While .Range("A" & RowCount) < "" Employee = .Range("A" & RowCount) ColCount = 2 Do While .Cells(RowCount, ColCount) < "" MyDate = .Cells(RowCount, ColCount) MyYear = Year(MyDate) MyMonth = Month(MyDate) With Sheets(CStr(MyYear)) 'check if employee already exists Set c = .Columns("A").Find(what:=Employee, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Cells(NewRow, MyMonth + 1) = MyDate .Cells(NewRow, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" Else .Cells(c.Row, MyMonth + 1) = MyDate .Cells(c.Row, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" End If End With ColCount = ColCount + 1 Loop RowCount = RowCount + 1 Loop End With End Sub "Barry" wrote: Yes I thought it did but the columns are still mixed up which ever way I tried it, either sort by row or column, it still did not put all Januray in the first column and all the febuary in thr next and so on. "Joel" wrote: It looks like Gord answer your question in another posting. "Barry" wrote: 19 June 2008 14 August 2008 09 October 2008 04 December 2008 13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
Hi Jole , yes that worked and the sheets have loaded ( which was really
impresive by the way), I have run the second macro ( movedates)and it has come up with Run-time error `9`: Subscript out of range And has highlighted :- With Sheets(CStr(MyYear)) Sorry I am really becoming a pain in the arse. "Joel" wrote: We must be using slighlty different version of VBA or with different settings. the problem is the inputbox is returning a string and on my PC it accepted the string and your computer it didn't try this change from StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear to StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = Val(StartYear) To Val(EndYear) "Barry" wrote: I have loaded as advised and loaded the dates (01/01/09) and (31/12/09) it has then come up with Run-time error `13`: Type mismatch, I pressed Debug and it highlighted with an arrow For MyYears = StartYear To EndYear. What did I do wrong? "Joel" wrote: From worksheet type ALT-F11 to get to VBA window Add a new module from VBA menu Insert Module Paste code into module sheet Type Alt F11 to get back to worksheet. Run macro from menu Tools - Macro - Maros and choose the correct macro. Run the macro to create the sheet first and only run the macro once. It will give you an error if you run it twice since the worksheets already exist. "Barry" wrote: Thank you Joel,Just to show my complete ignorance, where do I put the macros? "Joel" wrote: I wrote 2 macros. One to create the worksheets. the 2nd to put data into the worksheets. Sub createsheets() StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = MyYears For MyMonth = 1 To 12 Cells(1, MyMonth + 1) = MonthName(MyMonth) Next MyMonth Next MyYears End Sub Sub MoveDates() With Sheets("Sheet1") RowCount = 1 Do While .Range("A" & RowCount) < "" Employee = .Range("A" & RowCount) ColCount = 2 Do While .Cells(RowCount, ColCount) < "" MyDate = .Cells(RowCount, ColCount) MyYear = Year(MyDate) MyMonth = Month(MyDate) With Sheets(CStr(MyYear)) 'check if employee already exists Set c = .Columns("A").Find(what:=Employee, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Cells(NewRow, MyMonth + 1) = MyDate .Cells(NewRow, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" Else .Cells(c.Row, MyMonth + 1) = MyDate .Cells(c.Row, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" End If End With ColCount = ColCount + 1 Loop RowCount = RowCount + 1 Loop End With End Sub "Barry" wrote: Yes I thought it did but the columns are still mixed up which ever way I tried it, either sort by row or column, it still did not put all Januray in the first column and all the febuary in thr next and so on. "Joel" wrote: It looks like Gord answer your question in another posting. "Barry" wrote: 19 June 2008 14 August 2008 09 October 2008 04 December 2008 13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates in another sheet
CSTR() takes the number of the year like 2007 and makes it a string. the
tabs in the worksheet are strings and you entered a number. the error probaly occured becasue you have a year on the worksheet that you didn't include it the worksheet that made the worksheets. If you had a date 2006 and you only create sheets for the year 2007 to 2010 then you would get the Error 9. "Barry" wrote: Hi Jole , yes that worked and the sheets have loaded ( which was really impresive by the way), I have run the second macro ( movedates)and it has come up with Run-time error `9`: Subscript out of range And has highlighted :- With Sheets(CStr(MyYear)) Sorry I am really becoming a pain in the arse. "Joel" wrote: We must be using slighlty different version of VBA or with different settings. the problem is the inputbox is returning a string and on my PC it accepted the string and your computer it didn't try this change from StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear to StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = Val(StartYear) To Val(EndYear) "Barry" wrote: I have loaded as advised and loaded the dates (01/01/09) and (31/12/09) it has then come up with Run-time error `13`: Type mismatch, I pressed Debug and it highlighted with an arrow For MyYears = StartYear To EndYear. What did I do wrong? "Joel" wrote: From worksheet type ALT-F11 to get to VBA window Add a new module from VBA menu Insert Module Paste code into module sheet Type Alt F11 to get back to worksheet. Run macro from menu Tools - Macro - Maros and choose the correct macro. Run the macro to create the sheet first and only run the macro once. It will give you an error if you run it twice since the worksheets already exist. "Barry" wrote: Thank you Joel,Just to show my complete ignorance, where do I put the macros? "Joel" wrote: I wrote 2 macros. One to create the worksheets. the 2nd to put data into the worksheets. Sub createsheets() StartYear = InputBox("Enter start year : ") EndYear = InputBox("Enter end year : ") For MyYears = StartYear To EndYear Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = MyYears For MyMonth = 1 To 12 Cells(1, MyMonth + 1) = MonthName(MyMonth) Next MyMonth Next MyYears End Sub Sub MoveDates() With Sheets("Sheet1") RowCount = 1 Do While .Range("A" & RowCount) < "" Employee = .Range("A" & RowCount) ColCount = 2 Do While .Cells(RowCount, ColCount) < "" MyDate = .Cells(RowCount, ColCount) MyYear = Year(MyDate) MyMonth = Month(MyDate) With Sheets(CStr(MyYear)) 'check if employee already exists Set c = .Columns("A").Find(what:=Employee, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 .Cells(NewRow, MyMonth + 1) = MyDate .Cells(NewRow, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" Else .Cells(c.Row, MyMonth + 1) = MyDate .Cells(c.Row, MyMonth + 1).NumberFormat = _ "DD MMMM YYYY" End If End With ColCount = ColCount + 1 Loop RowCount = RowCount + 1 Loop End With End Sub "Barry" wrote: Yes I thought it did but the columns are still mixed up which ever way I tried it, either sort by row or column, it still did not put all Januray in the first column and all the febuary in thr next and so on. "Joel" wrote: It looks like Gord answer your question in another posting. "Barry" wrote: 19 June 2008 14 August 2008 09 October 2008 04 December 2008 13 July 2008 07 September 2008 02 November 2008 28 December 13 December 2007 07 February 2008 03 April 2008 29 May2008 This is how the dates comes in, this is three rows , I would like to copy and sort in another sheet, so all the junes from the three rows are in one column, so if it is column b then b1 would have any june dates from the first row, b2 would have any june dates from the second row and so on. "Joel" wrote: You'll need a macro. it is pretty easy to write one but I would like you to post some sample row of the data. Your explanation doesn't clearly specify everything I would need to write the macro. "Barry" wrote: Hi, I wonder if any one can help I have a sheet that has rows of dates in that look like this 31 October 2009 26 December 2009 20 February 2010 There are several rows and they are all have a persons name in the first cell A1 and a start date in the next A2, the other rows are populated with dates in two monthly intervals now because the start dates are all different they are really hard to read each month, What I would like to do is open a new sheet ( call it dates in order) and have 13 columns first one with a list of names and then the next 12 the months of the year, then I would like to transfer all the dates for January 2009 from the first sheet into the column headed January against the original name that is was linked to in the first sheet and so on, I would then make another sheet for 2010. I hope this makes sense, Can it be done? Thanks in advance , Barry. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting dates | Excel Discussion (Misc queries) | |||
Sorting Dates | Excel Discussion (Misc queries) | |||
Sorting dates | Excel Worksheet Functions | |||
Sorting Dates | Excel Discussion (Misc queries) | |||
Sorting dates | Excel Worksheet Functions |