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!

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!

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!

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!

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!

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!