splitting large worksheet by dates
Posted by jason on September 05, 2000 12:56 PM
I have been assigned the dubious task of setting up an Excel spreadsheet for another employee to use. My group maintains a large list that shows part defects by date. Currently we have one master list (named 'MASTER') that has the date the problem was identified (in mm/dd/yy format), along with a half dozen other columns that contain pertinent information.
We analyze all of the data by months. It would be ideal if there was a way to automatically extract all the data by months into seperate worksheets with appropriate names like mm/yy.
I would like to be able to have the worksheet setup so that whenever the sheet named 'MASTER' is modified, the data is automatically split up, while still maintaining the master list. Since data does not always come in chronologically, I would need it to update all of the sheets (including adding new ones whenever a month occurs that is not accounted for).
Thanks so much,
Jason
Posted by thomas venn on September 06, 0100 1:19 PM
You might want to try recording a macro for this task. first, you will need to add colunms so that you can extract the Month and Year to one column (use Month and Year functions). Then you will want to paste only the values. After which, you will need to sort by your new Month/Year column. In the column to the right of Month/Year, you may want to write a formula which would look something like this: if(a1=a2,"","different Month") what this will do is allow you to quickly identify where the months will be different. Then you will have to copy and paste values here as well, after you hit F9 for Calculate.
Ok, after you added these additional columns, (all this while you are recording your macro, go to the column where it is "different Month", copy this entire column to the right of the column and paste VALUES only (let us call this new column column K). this is important because the format is different. The next thing to do is go to cell L1, hit the end key once, the down arrow key once (which will take you to the end of the worksheet),the left arrow key once (which will take you to cell K65536). end key once, up arrow key once, end key once, up arrow key once.
***After you have completed all those steps, you should be at column K, in a cell with the value of "different Month". (let us say this is cell K230). the next thing you want to do is to arrow key all the way to cell A230. once you are in cell A230, press the F5 fuction key on your keyboard, and type in M655535, press the SHIFT key, then press Enter. this will select your range. now that your range is selected, you will now --> CUT, Insert New Worksheet, paste (in new worksheet). END RECORDING MACRO
This is a lot to think about. I don't know how familiar you are with VBA or Recording macros. The first thing you must do in this macro session is to use RELATIVE REFERENCE. You will also need to edit your macro and copy and paste the code where your code begins at this point -->(***After). You paste the code right before where it says "End Sub" in your VBA code.
Oh man, a lot to think about. but it is not at all that diffucult once you to record a few macros and see how the macro recording works.
As always, you should save your original data on to a new file so that when you mess up, you will still have the original data to go back to. there is code for the Tab name on the message board: Posted by Ivan Moala on September 03, 19100 at 01:31:36:
What you are asking for is probably 3 macros run seperately rather than one single macro.
Hope this helps. Cheers,
Thomas
