Extract Data to Creat Short List

  • Thread starter Thread starter Excelstein
  • Start date Start date
E

Excelstein

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!
 
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
 
Select Case TheMonth
Case 1: TheSht = "Jan"
Case 2: TheSht = "Feb"
Case 3: TheSht = "Mar"
Case 4: TheSht = "Apr"
Click to expand...

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
 
Dana
Thanks for that. I didn't know about MonthName. This learning stuff
never ends! Otto
 
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
 
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.)
 
Back
Top