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?


.

For those of you following this saga, Roger had the macro code in the
codemodule of the worksheet, not in a standard codemodule. All seems well
now.

Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Roger,

Send me your workbook, and I will take a look at the code. I promise not
to
look at your data <vbg.

Just take out the spaces and change the dot to a .

HTH,
Bernie
MS Excel MVP

"Roger" wrote in message
...
No blank lines, when I run the macro it creates one sheet
then there is a VBA pop up box with a round red bubble X
and "400" with the buttons "OK" and "Help" under it. It
doesn't give me a debug option.
-----Original Message-----
Roger,

It does loop, and it should make 12 sheets. Do you have
any blank lines
between groups of data? That would prevent it from
working properly.

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
Bernie,
You are my hero. But of coarse there is one last
thing.
vlookup works, and the loop works in the sense that it
filters all info into groups, but it only created one
new
worksheet (Group 1), I was hoping to have it create 12
worksheets, am I doing something wrong? Or do I have
to
run the macro 12 times to produce the 12 sheets? Is
there a way to loop that?
-----Original Message-----
37836 is actually August 3, 2003, 28 days before your
first date. (That way
the formula returned 1 rather than 0, stepping up
every
28 days....).

Anyway, since the dates are semirandom, you'll need to
make a lookup table:
put a table like this in E1:F13

Break Date Group
8/27/2003 Group 1
9/21/2003 Group 2
10/15/2003 Group 3
11/8/2003 Group 4
12/2/2003 Group 5
12/26/2003 Group 6
1/19/2004 Group 7
2/12/2004 Group 8
3/7/2004 Group 9
3/31/2004 Group 10
4/24/2004 Group 11
5/18/2004 Group 12

The break date should be the _first_ date in the
Grouping.

Then for a date in cell A1, use the formula

=VLOOKUP(A1,$E$1:$F$13,2)

and copy down to match.

Then use that column as the key for the macro.

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
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?


.



.



.

I sent it, let me know if it doen'st make it
thx
-----Original Message-----
Roger,

Send me your workbook, and I will take a look at the
code. I promise not to
look at your data <vbg.

Just take out the spaces and change the dot to a .

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
No blank lines, when I run the macro it creates one
sheet
then there is a VBA pop up box with a round red bubble
X
and "400" with the buttons "OK" and "Help" under it. It
doesn't give me a debug option.
-----Original Message-----
Roger,

It does loop, and it should make 12 sheets. Do you
have
any blank lines
between groups of data? That would prevent it from
working properly.

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
Bernie,
You are my hero. But of coarse there is one last
thing.
vlookup works, and the loop works in the sense that
it
filters all info into groups, but it only created
one
new
worksheet (Group 1), I was hoping to have it create
12
worksheets, am I doing something wrong? Or do I
have
to
run the macro 12 times to produce the 12 sheets? Is
there a way to loop that?
-----Original Message-----
37836 is actually August 3, 2003, 28 days before
your
first date. (That way
the formula returned 1 rather than 0, stepping up
every
28 days....).

Anyway, since the dates are semirandom, you'll
need to
make a lookup table:
put a table like this in E1:F13

Break Date Group
8/27/2003 Group 1
9/21/2003 Group 2
10/15/2003 Group 3
11/8/2003 Group 4
12/2/2003 Group 5
12/26/2003 Group 6
1/19/2004 Group 7
2/12/2004 Group 8
3/7/2004 Group 9
3/31/2004 Group 10
4/24/2004 Group 11
5/18/2004 Group 12

The break date should be the _first_ date in the
Grouping.

Then for a date in cell A1, use the formula

=VLOOKUP(A1,$E$1:$F$13,2)

and copy down to match.

Then use that column as the key for the macro.

HTH,
Bernie
MS Excel MVP

"Roger"
wrote in
message
...
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?


.



.



.



.

Ok since I already feel real dumb about all of this I
might as well go a bit deeper to say I don't know the
difference. But I did cut the code from the worksheet
module and paste it into my personal.xls macro file which
produced this run time error..

With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
--.SpecialCells(xlCellTypeVisible).Copy _
--mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter

The arrow is pointing to the section highlighted in
yellow when the debug button is clicked.


-----Original Message-----
For those of you following this saga, Roger had the
macro code in the
codemodule of the worksheet, not in a standard
codemodule. All seems well
now.

Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
message
...
Roger,

Send me your workbook, and I will take a look at the
code. I promise not
to
look at your data <vbg.

Just take out the spaces and change the dot to a .

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
No blank lines, when I run the macro it creates one
sheet
then there is a VBA pop up box with a round red
bubble X
and "400" with the buttons "OK" and "Help" under it.
It
doesn't give me a debug option.
-----Original Message-----
Roger,

It does loop, and it should make 12 sheets. Do you
have
any blank lines
between groups of data? That would prevent it from
working properly.

HTH,
Bernie
MS Excel MVP

"Roger" wrote
in
message
...
Bernie,
You are my hero. But of coarse there is one last
thing.
vlookup works, and the loop works in the sense
that it
filters all info into groups, but it only created
one
new
worksheet (Group 1), I was hoping to have it
create 12
worksheets, am I doing something wrong? Or do I
have
to
run the macro 12 times to produce the 12 sheets?
Is
there a way to loop that?
-----Original Message-----
37836 is actually August 3, 2003, 28 days
before your
first date. (That way
the formula returned 1 rather than 0, stepping up
every
28 days....).

Anyway, since the dates are semirandom, you'll
need to
make a lookup table:
put a table like this in E1:F13

Break Date Group
8/27/2003 Group 1
9/21/2003 Group 2
10/15/2003 Group 3
11/8/2003 Group 4
12/2/2003 Group 5
12/26/2003 Group 6
1/19/2004 Group 7
2/12/2004 Group 8
3/7/2004 Group 9
3/31/2004 Group 10
4/24/2004 Group 11
5/18/2004 Group 12

The break date should be the _first_ date in the
Grouping.

Then for a date in cell A1, use the formula

=VLOOKUP(A1,$E$1:$F$13,2)

and copy down to match.

Then use that column as the key for the macro.

HTH,
Bernie
MS Excel MVP

"Roger"
wrote in
message
...
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?


.



.



.





.

Roger,

I expect that you pasted it into the wrong type of codemodule again. The
macro works fine for me from my personal.xls.

Select your Personal.xls, the select Inset | module and paste your code into
the new module that is inserted.

HTH,
Bernie
MS Excel MVP

"Roger aka excel village idiot.."
wrote in message ...
Ok since I already feel real dumb about all of this I
might as well go a bit deeper to say I don't know the
difference. But I did cut the code from the worksheet
module and paste it into my personal.xls macro file which
produced this run time error..

With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
--.SpecialCells(xlCellTypeVisible).Copy _
--mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter

The arrow is pointing to the section highlighted in
yellow when the debug button is clicked.


-----Original Message-----
For those of you following this saga, Roger had the
macro code in the
codemodule of the worksheet, not in a standard
codemodule. All seems well
now.

Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
message
...
Roger,

Send me your workbook, and I will take a look at the
code. I promise not
to
look at your data <vbg.

Just take out the spaces and change the dot to a .

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
No blank lines, when I run the macro it creates one
sheet
then there is a VBA pop up box with a round red
bubble X
and "400" with the buttons "OK" and "Help" under it.
It
doesn't give me a debug option.
-----Original Message-----
Roger,

It does loop, and it should make 12 sheets. Do you
have
any blank lines
between groups of data? That would prevent it from
working properly.

HTH,
Bernie
MS Excel MVP

"Roger" wrote
in
message
...
Bernie,
You are my hero. But of coarse there is one last
thing.
vlookup works, and the loop works in the sense
that it
filters all info into groups, but it only created
one
new
worksheet (Group 1), I was hoping to have it
create 12
worksheets, am I doing something wrong? Or do I
have
to
run the macro 12 times to produce the 12 sheets?
Is
there a way to loop that?
-----Original Message-----
37836 is actually August 3, 2003, 28 days
before your
first date. (That way
the formula returned 1 rather than 0, stepping up
every
28 days....).

Anyway, since the dates are semirandom, you'll
need to
make a lookup table:
put a table like this in E1:F13

Break Date Group
8/27/2003 Group 1
9/21/2003 Group 2
10/15/2003 Group 3
11/8/2003 Group 4
12/2/2003 Group 5
12/26/2003 Group 6
1/19/2004 Group 7
2/12/2004 Group 8
3/7/2004 Group 9
3/31/2004 Group 10
4/24/2004 Group 11
5/18/2004 Group 12

The break date should be the _first_ date in the
Grouping.

Then for a date in cell A1, use the formula

=VLOOKUP(A1,$E$1:$F$13,2)

and copy down to match.

Then use that column as the key for the macro.

HTH,
Bernie
MS Excel MVP

"Roger"
wrote in
message
...
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?


.



.



.





.

Just a thought.
Why not send him is workbook back.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Roger,

I expect that you pasted it into the wrong type of codemodule again. The
macro works fine for me from my personal.xls.

Select your Personal.xls, the select Inset | module and paste your code
into
the new module that is inserted.

HTH,
Bernie
MS Excel MVP

"Roger aka excel village idiot.."
wrote in message ...
Ok since I already feel real dumb about all of this I
might as well go a bit deeper to say I don't know the
difference. But I did cut the code from the worksheet
module and paste it into my personal.xls macro file which
produced this run time error..

With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
--.SpecialCells(xlCellTypeVisible).Copy _
--mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter

The arrow is pointing to the section highlighted in
yellow when the debug button is clicked.


-----Original Message-----
For those of you following this saga, Roger had the
macro code in the
codemodule of the worksheet, not in a standard
codemodule. All seems well
now.

Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
message
...
Roger,

Send me your workbook, and I will take a look at the
code. I promise not
to
look at your data <vbg.

Just take out the spaces and change the dot to a .

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
No blank lines, when I run the macro it creates one
sheet
then there is a VBA pop up box with a round red
bubble X
and "400" with the buttons "OK" and "Help" under it.
It
doesn't give me a debug option.
-----Original Message-----
Roger,

It does loop, and it should make 12 sheets. Do you
have
any blank lines
between groups of data? That would prevent it from
working properly.

HTH,
Bernie
MS Excel MVP

"Roger" wrote
in
message
...
Bernie,
You are my hero. But of coarse there is one last
thing.
vlookup works, and the loop works in the sense
that it
filters all info into groups, but it only created
one
new
worksheet (Group 1), I was hoping to have it
create 12
worksheets, am I doing something wrong? Or do I
have
to
run the macro 12 times to produce the 12 sheets?
Is
there a way to loop that?
-----Original Message-----
37836 is actually August 3, 2003, 28 days
before your
first date. (That way
the formula returned 1 rather than 0, stepping up
every
28 days....).

Anyway, since the dates are semirandom, you'll
need to
make a lookup table:
put a table like this in E1:F13

Break Date Group
8/27/2003 Group 1
9/21/2003 Group 2
10/15/2003 Group 3
11/8/2003 Group 4
12/2/2003 Group 5
12/26/2003 Group 6
1/19/2004 Group 7
2/12/2004 Group 8
3/7/2004 Group 9
3/31/2004 Group 10
4/24/2004 Group 11
5/18/2004 Group 12

The break date should be the _first_ date in the
Grouping.

Then for a date in cell A1, use the formula

=VLOOKUP(A1,$E$1:$F$13,2)

and copy down to match.

Then use that column as the key for the macro.

HTH,
Bernie
MS Excel MVP

"Roger"
wrote in
message
...
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?


.



.



.





.

Tom,

I did send him a working version, but then he cut and pasted his macro into
his personal.xls, so I guess I'll have to send him a personal.xls, too.

Bernie

"Tom Ogilvy" wrote in message
...
Just a thought.
Why not send him is workbook back.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Roger,

I expect that you pasted it into the wrong type of codemodule again.
The
macro works fine for me from my personal.xls.

Select your Personal.xls, the select Inset | module and paste your code
into
the new module that is inserted.

HTH,
Bernie
MS Excel MVP

"Roger aka excel village idiot.."
wrote in message ...
Ok since I already feel real dumb about all of this I
might as well go a bit deeper to say I don't know the
difference. But I did cut the code from the worksheet
module and paste it into my personal.xls macro file which
produced this run time error..

With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
--.SpecialCells(xlCellTypeVisible).Copy _
--mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter

The arrow is pointing to the section highlighted in
yellow when the debug button is clicked.


-----Original Message-----
For those of you following this saga, Roger had the
macro code in the
codemodule of the worksheet, not in a standard
codemodule. All seems well
now.

Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
message
...
Roger,

Send me your workbook, and I will take a look at the
code. I promise not
to
look at your data <vbg.

Just take out the spaces and change the dot to a .

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
No blank lines, when I run the macro it creates one
sheet
then there is a VBA pop up box with a round red
bubble X
and "400" with the buttons "OK" and "Help" under it.
It
doesn't give me a debug option.
-----Original Message-----
Roger,

It does loop, and it should make 12 sheets. Do you
have
any blank lines
between groups of data? That would prevent it from
working properly.

HTH,
Bernie
MS Excel MVP

"Roger" wrote
in
message
...
Bernie,
You are my hero. But of coarse there is one last
thing.
vlookup works, and the loop works in the sense
that it
filters all info into groups, but it only created
one
new
worksheet (Group 1), I was hoping to have it
create 12
worksheets, am I doing something wrong? Or do I
have
to
run the macro 12 times to produce the 12 sheets?
Is
there a way to loop that?
-----Original Message-----
37836 is actually August 3, 2003, 28 days
before your
first date. (That way
the formula returned 1 rather than 0, stepping up
every
28 days....).

Anyway, since the dates are semirandom, you'll
need to
make a lookup table:
put a table like this in E1:F13

Break Date Group
8/27/2003 Group 1
9/21/2003 Group 2
10/15/2003 Group 3
11/8/2003 Group 4
12/2/2003 Group 5
12/26/2003 Group 6
1/19/2004 Group 7
2/12/2004 Group 8
3/7/2004 Group 9
3/31/2004 Group 10
4/24/2004 Group 11
5/18/2004 Group 12

The break date should be the _first_ date in the
Grouping.

Then for a date in cell A1, use the formula

=VLOOKUP(A1,$E$1:$F$13,2)

and copy down to match.

Then use that column as the key for the macro.

HTH,
Bernie
MS Excel MVP

"Roger"
wrote in
message
...
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?


.



.



.





.

Tom (and anybody else who is interested),

The workbook with Roger's data was generated by a query, and when he copied
it into a new workbook, the macro worked. Otherwise, it was hanging on the
paste part of the copy and paste.

Bernie


"Tom Ogilvy" wrote in message
...
Just a thought.
Why not send him is workbook back.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Roger,

I expect that you pasted it into the wrong type of codemodule again.
The
macro works fine for me from my personal.xls.

Select your Personal.xls, the select Inset | module and paste your code
into
the new module that is inserted.

HTH,
Bernie
MS Excel MVP

"Roger aka excel village idiot.."
wrote in message ...
Ok since I already feel real dumb about all of this I
might as well go a bit deeper to say I don't know the
difference. But I did cut the code from the worksheet
module and paste it into my personal.xls macro file which
produced this run time error..

With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
--.SpecialCells(xlCellTypeVisible).Copy _
--mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter

The arrow is pointing to the section highlighted in
yellow when the debug button is clicked.


-----Original Message-----
For those of you following this saga, Roger had the
macro code in the
codemodule of the worksheet, not in a standard
codemodule. All seems well
now.

Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
message
...
Roger,

Send me your workbook, and I will take a look at the
code. I promise not
to
look at your data <vbg.

Just take out the spaces and change the dot to a .

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
No blank lines, when I run the macro it creates one
sheet
then there is a VBA pop up box with a round red
bubble X
and "400" with the buttons "OK" and "Help" under it.
It
doesn't give me a debug option.
-----Original Message-----
Roger,

It does loop, and it should make 12 sheets. Do you
have
any blank lines
between groups of data? That would prevent it from
working properly.

HTH,
Bernie
MS Excel MVP

"Roger" wrote
in
message
...
Bernie,
You are my hero. But of coarse there is one last
thing.
vlookup works, and the loop works in the sense
that it
filters all info into groups, but it only created
one
new
worksheet (Group 1), I was hoping to have it
create 12
worksheets, am I doing something wrong? Or do I
have
to
run the macro 12 times to produce the 12 sheets?
Is
there a way to loop that?
-----Original Message-----
37836 is actually August 3, 2003, 28 days
before your
first date. (That way
the formula returned 1 rather than 0, stepping up
every
28 days....).

Anyway, since the dates are semirandom, you'll
need to
make a lookup table:
put a table like this in E1:F13

Break Date Group
8/27/2003 Group 1
9/21/2003 Group 2
10/15/2003 Group 3
11/8/2003 Group 4
12/2/2003 Group 5
12/26/2003 Group 6
1/19/2004 Group 7
2/12/2004 Group 8
3/7/2004 Group 9
3/31/2004 Group 10
4/24/2004 Group 11
5/18/2004 Group 12

The break date should be the _first_ date in the
Grouping.

Then for a date in cell A1, use the formula

=VLOOKUP(A1,$E$1:$F$13,2)

and copy down to match.

Then use that column as the key for the macro.

HTH,
Bernie
MS Excel MVP

"Roger"
wrote in
message
...
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?


.



.



.





.

Thanks for the feed back - so I guess the workbook was screwed up.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Tom (and anybody else who is interested),

The workbook with Roger's data was generated by a query, and when he
copied
it into a new workbook, the macro worked. Otherwise, it was hanging on
the
paste part of the copy and paste.

Bernie


"Tom Ogilvy" wrote in message
...
Just a thought.
Why not send him is workbook back.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Roger,

I expect that you pasted it into the wrong type of codemodule again.
The
macro works fine for me from my personal.xls.

Select your Personal.xls, the select Inset | module and paste your
code
into
the new module that is inserted.

HTH,
Bernie
MS Excel MVP

"Roger aka excel village idiot.."

wrote in message ...
Ok since I already feel real dumb about all of this I
might as well go a bit deeper to say I don't know the
difference. But I did cut the code from the worksheet
module and paste it into my personal.xls macro file which
produced this run time error..

With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
--.SpecialCells(xlCellTypeVisible).Copy _
--mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter

The arrow is pointing to the section highlighted in
yellow when the debug button is clicked.


-----Original Message-----
For those of you following this saga, Roger had the
macro code in the
codemodule of the worksheet, not in a standard
codemodule. All seems well
now.

Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
message
...
Roger,

Send me your workbook, and I will take a look at the
code. I promise not
to
look at your data <vbg.

Just take out the spaces and change the dot to a .

HTH,
Bernie
MS Excel MVP

"Roger" wrote in
message
...
No blank lines, when I run the macro it creates one
sheet
then there is a VBA pop up box with a round red
bubble X
and "400" with the buttons "OK" and "Help" under it.
It
doesn't give me a debug option.
-----Original Message-----
Roger,

It does loop, and it should make 12 sheets. Do you
have
any blank lines
between groups of data? That would prevent it from
working properly.

HTH,
Bernie
MS Excel MVP

"Roger" wrote
in
message
...
Bernie,
You are my hero. But of coarse there is one last
thing.
vlookup works, and the loop works in the sense
that it
filters all info into groups, but it only created
one
new
worksheet (Group 1), I was hoping to have it
create 12
worksheets, am I doing something wrong? Or do I
have
to
run the macro 12 times to produce the 12 sheets?
Is
there a way to loop that?
-----Original Message-----
37836 is actually August 3, 2003, 28 days
before your
first date. (That way
the formula returned 1 rather than 0, stepping up
every
28 days....).

Anyway, since the dates are semirandom, you'll
need to
make a lookup table:
put a table like this in E1:F13

Break Date Group
8/27/2003 Group 1
9/21/2003 Group 2
10/15/2003 Group 3
11/8/2003 Group 4
12/2/2003 Group 5
12/26/2003 Group 6
1/19/2004 Group 7
2/12/2004 Group 8
3/7/2004 Group 9
3/31/2004 Group 10
4/24/2004 Group 11
5/18/2004 Group 12

The break date should be the _first_ date in the
Grouping.

Then for a date in cell A1, use the formula

=VLOOKUP(A1,$E$1:$F$13,2)

and copy down to match.

Then use that column as the key for the macro.

HTH,
Bernie
MS Excel MVP

"Roger"
wrote in
message
...
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?


.



.



.





.