I have a list of data that I would like to go through and
extract entire rows into other worksheets depending on a
date range. The data appears in colums A thru E and the
date is in colum C. Is there a loop that can be writen
that will seach colum C for certain date ranges and then
extract the entire row and place it in a new worksheet in
the same workbook. Ultimatly I would have 12 different
date ranges, creating 12 differnt worksheets. I've
exhausted my patients on trying to figuer it out myself.

thanks in advance
Roger

Roger,

It would be simplest if you could insert a new column with a formula that
would give a unique identifier (it is simplest because I have a macro that
will do it, without me having to change the macro <vbg). Let's say that
you want to base the extraction on months: use the formula

=TEXT(A2,"mmmm")

where A2 is a cell in your column of Dates.

Then select a cell in your datatable, and use the macro below. Enter the
relative column number of your new column with months when prompted.

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")


Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub


"Roger" wrote in message
...
I have a list of data that I would like to go through and
extract entire rows into other worksheets depending on a
date range. The data appears in colums A thru E and the
date is in colum C. Is there a loop that can be writen
that will seach colum C for certain date ranges and then
extract the entire row and place it in a new worksheet in
the same workbook. Ultimatly I would have 12 different
date ranges, creating 12 differnt worksheets. I've
exhausted my patients on trying to figuer it out myself.

thanks in advance
Roger

First is there a formula that I could use to define a
date range as an identifier eg rng 1 as 8/31/2003 to
9/27/2003?
I did the rest with the month name as an identifier and
when promted I entered the colum name (colum C) I
recieved the promt " type mismatch" what am I doing wrong?
-----Original Message-----
Roger,

It would be simplest if you could insert a new column
with a formula that
would give a unique identifier (it is simplest because I
have a macro that
will do it, without me having to change the macro
<vbg). Let's say that
you want to base the extraction on months: use the
formula

=TEXT(A2,"mmmm")

where A2 is a cell in your column of Dates.

Then select a cell in your datatable, and use the macro
below. Enter the
relative column number of your new column with months
when prompted.

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use
as key?")


Set myArea = ActiveCell.CurrentRegion.Columns
(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(befo=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub


"Roger" wrote in
message
...
I have a list of data that I would like to go through
and
extract entire rows into other worksheets depending on
a
date range. The data appears in colums A thru E and
the
date is in colum C. Is there a loop that can be writen
that will seach colum C for certain date ranges and
then
extract the entire row and place it in a new worksheet
in
the same workbook. Ultimatly I would have 12 different
date ranges, creating 12 differnt worksheets. I've
exhausted my patients on trying to figuer it out
myself.

thanks in advance
Roger


.

Roger,

The formula could be something like

="Group " & INT((A2-37836)/28)
(if A2 is 8/31/03)

There are all sorts of possibilities depending on your break out scheme -
which seems like four weeks....

Anyway, when you are prompted for a column number, if you have your database
in columns B to H, and the key is in column C, then you would enter a 2.

HTH,
Bernie
MS Excel MVP

"Roger" wrote in message
...
First is there a formula that I could use to define a
date range as an identifier eg rng 1 as 8/31/2003 to
9/27/2003?
I did the rest with the month name as an identifier and
when promted I entered the colum name (colum C) I
recieved the promt " type mismatch" what am I doing wrong?

Bernie,
the break out scheme is a wierd one, it's 12 periods
("months") that are semi random date ranges. would I
have to define each range as a variable, and if so how?
Would this be a better macro than a formula? Sorry for
all of the questions... Could you also explain the
sample formula that you gave me below. What does the
37836 stand for?
Roger
-----Original Message-----
Roger,

The formula could be something like

="Group " & INT((A2-37836)/28)
(if A2 is 8/31/03)

There are all sorts of possibilities depending on your
break out scheme -
which seems like four weeks....

Anyway, when you are prompted for a column number, if
you have your database
in columns B to H, and the key is in column C, then you
would enter a 2.

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
First is there a formula that I could use to define a
date range as an identifier eg rng 1 as 8/31/2003 to
9/27/2003?
I did the rest with the month name as an identifier and
when promted I entered the colum name (colum C) I
recieved the promt " type mismatch" what am I doing
wrong?


.