Date selection loop

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

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(before:=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
 
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,

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
 
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
 
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
 
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?
 
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
 
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.
 
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
 
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
 
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




.
Click to expand...
 
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.
 
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
 
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 (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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with looping in Excel 5
macro that extracts specific data from multiple worksheets 0
Excel VBA 1
InputBox for column letter problem 2
Combine three InputBoxes into one? 7
Arithmetic with Cell Dates 1
Delete rows based on date criteria 1
looping thru rows 11

Back
Top