Move data to new sheet - rename sheet based on criteria ?

[Michael.Ray.Pennington]

Hey guys, any help is appreciated. Here is my situation: I have a
spreadsheet with over 4000 rows of data. The data has the month end
date in column A, but there is an arbitrary number of rows for each
month end. Is there a way to move the rows of data that all have the
same month end date to a new sheet named for the corresponding date.
As there are over 10 years of month end data, this is quite arduous
without a macro.

 

[Don Guillett]

Properly formatted dates in what column?
Is data sorted?
copy/paste sample layout.
etc.
Why not just use data>filter>autofilter or a pivot table?

 

[Roger Govier]

Hi Michael

Why do you want to move it to different sheets?
It is normally better to keep the data in one place, and use Filters or
Pivot Tables to pull out the data you want.

Assuming you have a header in row 1, highlight the headings and
Data>Filter>Autofilter.
Use the dropdown on column A to select just those rows belong to that
month end.
If you want, you could then copy those rows and Paste to another sheet.

Alternatively, Data>Filter Advanced Filter could be use, with criteria
set up on a second sheet to bring just the matching rows of data across.

For more help on Advanced Filter (and Auto filter) take a look at Debra
Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs

A pivot Table may well be the best solution for you, so check out Pivot
Tables whilst at Debra's site and her introduction at
http://peltiertech.com/Excel/Pivots/pivottables.htm

 

[Ron de Bruin]

You can install Easyfilter
http://www.rondebruin.nl/easyfilter.htm

Or you can adapt the code on this page
http://www.rondebruin.nl/copy5.htm

 

[Michael.Ray.Pennington]

Date Stock Ticker Security # Shares/Par Price Value
12/31/2007 Google GOOG 12345678 10 $458.00 $4,580
12/31/2006 Microsoft MSFT 12456789 8 $31.30 $250

Dates in column A and is sorted. Autofilter works well to view the
data, but I am needing it in new sheet for each date. (Not my idea).
Is there a function in pivot table to create a new sheet for each
unique value? I seem to remember something like that, but I am not
sure how it works.

 

[Roger Govier]

Hi
If you make the date a Page field, then from the PT toolbar choose Show
pages, a sheet will be created for items in the page area, with all of
the lines relating to that value.

Be aware however, that you cannot Group dates within a Page area.
If you do want to group dates by month / year. then drag the Date filed
first to the Row area, do the grouping, then drag year and Month to the
Page area

 

[Don Guillett]

Send me a sample workbook and desires and I will take a look.
BTW, I once had a series 7 as a regional mgr for ING

 

[Don Guillett]

I sent this

Sub SeparateSheets()
On Error Resume Next
With Sheets("sheet1")
wc = .Rows(1).Find("Date").Column
lr = .Cells(Rows.Count, wc).End(xlUp).Row
mc = 2
Do Until x >= lr

shname = Format(.Cells(mc, wc), "mmm-yy")
Sheets("Template").Copy after:=Sheets(Worksheets.Count)
ActiveSheet.Name = shname

x = Application.Match(.Cells(mc, wc), .Columns(wc))
..Rows(mc & ":" & x).Copy Sheets(shname).Range("a2")
mc = x + 1
Loop
End With
End Sub