Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
move rows of data seperated in a sheet to a sheet with no separat | Excel Worksheet Functions | |||
Copy entire row to another sheet based on a criteria | Excel Discussion (Misc queries) | |||
Need to look up value in another Sheet based on criteria in first sheet. | Excel Worksheet Functions | |||
Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
Insert sheet, move to end, rename with cell data. | Excel Discussion (Misc queries) |