Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Move data to new sheet - rename sheet based on criteria ?

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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Move data to new sheet - rename sheet based on criteria ?

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

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Move data to new sheet - rename sheet based on criteria ?

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
DataFilterAutofilter.
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, DataFilter 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


--
Regards

Roger Govier


wrote in message
oups.com...
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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,123
Default Move data to new sheet - rename sheet based on criteria ?

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.com...
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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Move data to new sheet - rename sheet based on criteria ?


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.

On May 15, 12:25 pm, "Don Guillett" wrote:
Properly formatted dates in what column?
Isdatasorted?
copy/paste sample layout.
etc.
Why not just usedatafilterautofilter or a pivot table?

--
Don Guillett
SalesAid Software
...@gm ail.com wrote in message

oups.com...

Hey guys, any help is appreciated. Here is my situation: I have a
spreadsheet with over 4000 rows ofdata. Thedatahas the month end
date in column A, but there is an arbitrary number of rows for each
month end. Is there a way tomovethe rows ofdatathat all have the
same month end date to anewsheetnamed for the corresponding date.
As there are over 10 years of month enddata, this is quite arduous
without a macro.





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Move data to new sheet - rename sheet based on criteria ?

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

--
Regards

Roger Govier


wrote in message
oups.com...

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.

On May 15, 12:25 pm, "Don Guillett" wrote:
Properly formatted dates in what column?
Isdatasorted?
copy/paste sample layout.
etc.
Why not just usedatafilterautofilter or a pivot table?

--
Don Guillett
SalesAid Software
...@gm ail.com wrote in
message

oups.com...

Hey guys, any help is appreciated. Here is my situation: I have a
spreadsheet with over 4000 rows ofdata. Thedatahas the month end
date in column A, but there is an arbitrary number of rows for each
month end. Is there a way tomovethe rows ofdatathat all have the
same month end date to anewsheetnamed for the corresponding date.
As there are over 10 years of month enddata, this is quite arduous
without a macro.





  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Move data to new sheet - rename sheet based on criteria ?

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

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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
SalesAid Software

wrote in message
oups.com...

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.

On May 15, 12:25 pm, "Don Guillett" wrote:
Properly formatted dates in what column?
Isdatasorted?
copy/paste sample layout.
etc.
Why not just usedatafilterautofilter or a pivot table?

--
Don Guillett
SalesAid Software
...@gm ail.com wrote in
message

oups.com...

Hey guys, any help is appreciated. Here is my situation: I have a
spreadsheet with over 4000 rows ofdata. Thedatahas the month end
date in column A, but there is an arbitrary number of rows for each
month end. Is there a way tomovethe rows ofdatathat all have the
same month end date to anewsheetnamed for the corresponding date.
As there are over 10 years of month enddata, this is quite arduous
without a macro.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
move rows of data seperated in a sheet to a sheet with no separat Lynn Excel Worksheet Functions 5 December 22nd 06 04:18 AM
Copy entire row to another sheet based on a criteria Brig Siton Excel Discussion (Misc queries) 3 August 7th 06 09:04 PM
Need to look up value in another Sheet based on criteria in first sheet. Stacey Excel Worksheet Functions 1 March 28th 06 06:22 AM
Pull data from another sheet based on certain criteria steve_sr2 Excel Discussion (Misc queries) 1 February 23rd 06 11:08 AM
Insert sheet, move to end, rename with cell data. [email protected] Excel Discussion (Misc queries) 2 October 11th 05 03:04 PM


All times are GMT +1. The time now is 05:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"