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?


.



.



.





.