Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract Data to Creat Short List
Hi Anyone!
I need help in picking rows of data from one worksheet based on selective criteria and adding the rows to different sheets based on the selective criteria. For example: data is added to sheet 1 with different dates in column A. Based on the month in the date, the information on the same row is transferred to different sheets for different months. Please Help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract Data to Creat Short List
This macro will do what you want. I assumed your data is in a sheet named
"Main", you have 12 other sheets named Jan, Feb, etc, your data in the Main sheets starts in row 2, each of the month sheets has headers in row 1, you want to copy columns A:J (10 columns. Change these parameters in the code as needed to fit your actual data. HTH Otto Sub CopyRows() Dim rColA As Range, i As Range Dim TheMonth As Long, TheSht As String Sheets("Main").Select Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In rColA TheMonth = Month(i) Select Case TheMonth Case 1: TheSht = "Jan" Case 2: TheSht = "Feb" Case 3: TheSht = "Mar" Case 4: TheSht = "Apr" Case 5: TheSht = "May" Case 6: TheSht = "Jun" Case 7: TheSht = "Jul" Case 8: TheSht = "Aug" Case 9: TheSht = "Sep" Case 10: TheSht = "Oct" Case 11: TheSht = "Nov" Case 12: TheSht = "Dec" End Select With Sheets(TheSht) i.Resize(, 10).Copy .Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial End With Next i End Sub "Excelstein" wrote in message ... Hi Anyone! I need help in picking rows of data from one worksheet based on selective criteria and adding the rows to different sheets based on the selective criteria. For example: data is added to sheet 1 with different dates in column A. Based on the month in the date, the information on the same row is transferred to different sheets for different months. Please Help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract Data to Creat Short List
Select Case TheMonth
Case 1: TheSht = "Jan" Case 2: TheSht = "Feb" Case 3: TheSht = "Mar" Case 4: TheSht = "Apr" Hi. Just an idea... For Each i In rColA TheMonth = CLng(Month(i)) Select Case TheMonth Case 1 To 12 TheSht = MonthName(TheMonth, True) Case Else 'error ... End Select On 1/9/2010 1:01 PM, Otto Moehrbach wrote: This macro will do what you want. I assumed your data is in a sheet named "Main", you have 12 other sheets named Jan, Feb, etc, your data in the Main sheets starts in row 2, each of the month sheets has headers in row 1, you want to copy columns A:J (10 columns. Change these parameters in the code as needed to fit your actual data. HTH Otto Sub CopyRows() Dim rColA As Range, i As Range Dim TheMonth As Long, TheSht As String Sheets("Main").Select Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In rColA TheMonth = Month(i) Select Case TheMonth Case 1: TheSht = "Jan" Case 2: TheSht = "Feb" Case 3: TheSht = "Mar" Case 4: TheSht = "Apr" Case 5: TheSht = "May" Case 6: TheSht = "Jun" Case 7: TheSht = "Jul" Case 8: TheSht = "Aug" Case 9: TheSht = "Sep" Case 10: TheSht = "Oct" Case 11: TheSht = "Nov" Case 12: TheSht = "Dec" End Select With Sheets(TheSht) i.Resize(, 10).Copy .Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial End With Next i End Sub "Excelstein" wrote in message ... Hi Anyone! I need help in picking rows of data from one worksheet based on selective criteria and adding the rows to different sheets based on the selective criteria. For example: data is added to sheet 1 with different dates in column A. Based on the month in the date, the information on the same row is transferred to different sheets for different months. Please Help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract Data to Creat Short List
Dana
Thanks for that. I didn't know about MonthName. This learning stuff never ends! Otto "Dana DeLouis" wrote in message ... Select Case TheMonth Case 1: TheSht = "Jan" Case 2: TheSht = "Feb" Case 3: TheSht = "Mar" Case 4: TheSht = "Apr" Hi. Just an idea... For Each i In rColA TheMonth = CLng(Month(i)) Select Case TheMonth Case 1 To 12 TheSht = MonthName(TheMonth, True) Case Else 'error ... End Select On 1/9/2010 1:01 PM, Otto Moehrbach wrote: This macro will do what you want. I assumed your data is in a sheet named "Main", you have 12 other sheets named Jan, Feb, etc, your data in the Main sheets starts in row 2, each of the month sheets has headers in row 1, you want to copy columns A:J (10 columns. Change these parameters in the code as needed to fit your actual data. HTH Otto Sub CopyRows() Dim rColA As Range, i As Range Dim TheMonth As Long, TheSht As String Sheets("Main").Select Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In rColA TheMonth = Month(i) Select Case TheMonth Case 1: TheSht = "Jan" Case 2: TheSht = "Feb" Case 3: TheSht = "Mar" Case 4: TheSht = "Apr" Case 5: TheSht = "May" Case 6: TheSht = "Jun" Case 7: TheSht = "Jul" Case 8: TheSht = "Aug" Case 9: TheSht = "Sep" Case 10: TheSht = "Oct" Case 11: TheSht = "Nov" Case 12: TheSht = "Dec" End Select With Sheets(TheSht) i.Resize(, 10).Copy .Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial End With Next i End Sub "Excelstein" wrote in message ... Hi Anyone! I need help in picking rows of data from one worksheet based on selective criteria and adding the rows to different sheets based on the selective criteria. For example: data is added to sheet 1 with different dates in column A. Based on the month in the date, the information on the same row is transferred to different sheets for different months. Please Help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract Data to Creat Short List
Hi. I like it better than the "old" way :)
Sub Demo() Dim m m = 8 Debug.Print MonthName(m, True) 'Old way... Debug.Print Application.GetCustomListContents(3)(m) End Sub Returns: Aug Aug = = = = = = = Dana DeLouis On 1/10/2010 10:41 AM, Otto Moehrbach wrote: Dana Thanks for that. I didn't know about MonthName. This learning stuff never ends! Otto "Dana DeLouis" wrote in message ... Select Case TheMonth Case 1: TheSht = "Jan" Case 2: TheSht = "Feb" Case 3: TheSht = "Mar" Case 4: TheSht = "Apr" Hi. Just an idea... For Each i In rColA TheMonth = CLng(Month(i)) Select Case TheMonth Case 1 To 12 TheSht = MonthName(TheMonth, True) Case Else 'error ... End Select On 1/9/2010 1:01 PM, Otto Moehrbach wrote: This macro will do what you want. I assumed your data is in a sheet named "Main", you have 12 other sheets named Jan, Feb, etc, your data in the Main sheets starts in row 2, each of the month sheets has headers in row 1, you want to copy columns A:J (10 columns. Change these parameters in the code as needed to fit your actual data. HTH Otto Sub CopyRows() Dim rColA As Range, i As Range Dim TheMonth As Long, TheSht As String Sheets("Main").Select Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In rColA TheMonth = Month(i) Select Case TheMonth Case 1: TheSht = "Jan" Case 2: TheSht = "Feb" Case 3: TheSht = "Mar" Case 4: TheSht = "Apr" Case 5: TheSht = "May" Case 6: TheSht = "Jun" Case 7: TheSht = "Jul" Case 8: TheSht = "Aug" Case 9: TheSht = "Sep" Case 10: TheSht = "Oct" Case 11: TheSht = "Nov" Case 12: TheSht = "Dec" End Select With Sheets(TheSht) i.Resize(, 10).Copy .Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial End With Next i End Sub "Excelstein" wrote in message ... Hi Anyone! I need help in picking rows of data from one worksheet based on selective criteria and adding the rows to different sheets based on the selective criteria. For example: data is added to sheet 1 with different dates in column A. Based on the month in the date, the information on the same row is transferred to different sheets for different months. Please Help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract Data to Creat Short List
You can use AutoFilter on the first spreadsheet. Then, for example, use a
custom filter on the date column to choose dates in the month range you want. Then you can copy/paste these into the appropriate month's worksheet. With AutoFilter, you can set filters on several columns at once, which gives you a lot of flexibility. (If you only need date criteria for this, then I would sort the first spreadsheet by date, then copy/paste the rows to the month spreadsheets.) -- Daryl S "Excelstein" wrote: Hi Anyone! I need help in picking rows of data from one worksheet based on selective criteria and adding the rows to different sheets based on the selective criteria. For example: data is added to sheet 1 with different dates in column A. Based on the month in the date, the information on the same row is transferred to different sheets for different months. Please Help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to choose data from list using key board short cuts | Excel Discussion (Misc queries) | |||
Short listing the Data Validation List | Excel Discussion (Misc queries) | |||
Function to extract data from a list | Excel Worksheet Functions | |||
extract data from a random list & place in another ordered list | Excel Discussion (Misc queries) | |||
Extract data value using pick list | Excel Worksheet Functions |