 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2007Move 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. 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?  SignatureDon Guillett SalesAid Software dguillett1@austin.rr.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 [quoted text clipped - 3 lines] > As there are over 10 years of month end data, this is quite arduous > without a macro. 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. > Properly formatted dates in what column? > Isdatasorted? [quoted text clipped - 16 lines] > > As there are over 10 years of month enddata, this is quite arduous > > without a macro. 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 SignatureRegards Roger Govier > Date Stock Ticker Security # Shares/Par Price Value > 12/31/2007 Google GOOG 12345678 10 $458.00 $4,580 [quoted text clipped - 27 lines] >> > As there are over 10 years of month enddata, this is quite arduous >> > without a macro. 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 SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > > Date Stock Ticker Security # Shares/Par Price Value [quoted text clipped - 28 lines] >> > As there are over 10 years of month enddata, this is quite arduous >> > without a macro. 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 SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > 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 [quoted text clipped - 30 lines] >>> > As there are over 10 years of month enddata, this is quite arduous >>> > without a macro. 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 SignatureRegards Roger Govier > 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 [quoted text clipped - 3 lines] > As there are over 10 years of month end data, this is quite arduous > without a macro. 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 SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > 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 [quoted text clipped - 3 lines] > As there are over 10 years of month end data, this is quite arduous > without a macro. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.