Home |
Search |
Today's Posts |
#1
|
|||
|
|||
splitting 1 sheet to multiple sheets
I think I posted this in the wrong forum, so I am re-posting it here. Any help would be greatly appreciated. Thank you! Hello everyone. I need some help, and always seem to find my answers here! I have a spreadsheet with a bunch of different accounts on it. Column F contains a code which determines what group the account belongs in. I need to split the data into separate spreadsheets based on this code. in addition, every code will not be there everytime. How do I write a macro to evaluate column F, and take all accounts with certain codes and move them to a new sheet. Also, how do I stop the macro from erroring out, if one of the codes does not exist? Here is a sample of my spreadsheet: A B C D E F EN4590 TX 4/30/2004 544.1 DD MBOEAS-3 EN1155 IL 5/21/2004 503.85 ER MBOAEA-3 DT8455 IL 8/18/2003 165.44 GS MBOAEE-3 EN4590 TX 4/30/2004 544.1 WB MBOQBL-1 EN1155 IL 5/21/2004 503.85 IF MBOCA1-1 in this example, all rows would be split to their own spreadsheet. If there was more than one account with the same code in F, they would go to the same spreadsheet. I do need to hard code the various codes into the Macro, as the spreadsheets will be saved with names relating to those codes, so I need a way to prevent errors if a certain code does not exist. Please help! Thank you! -- dfeld71 ------------------------------------------------------------------------ dfeld71's Profile: http://www.excelforum.com/member.php...o&userid=21452 View this thread: http://www.excelforum.com/showthread...hreadid=377809 |
#2
|
|||
|
|||
Try this
http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl "dfeld71" wrote in message ... I think I posted this in the wrong forum, so I am re-posting it here. Any help would be greatly appreciated. Thank you! Hello everyone. I need some help, and always seem to find my answers here! I have a spreadsheet with a bunch of different accounts on it. Column F contains a code which determines what group the account belongs in. I need to split the data into separate spreadsheets based on this code. in addition, every code will not be there everytime. How do I write a macro to evaluate column F, and take all accounts with certain codes and move them to a new sheet. Also, how do I stop the macro from erroring out, if one of the codes does not exist? Here is a sample of my spreadsheet: A B C D E F EN4590 TX 4/30/2004 544.1 DD MBOEAS-3 EN1155 IL 5/21/2004 503.85 ER MBOAEA-3 DT8455 IL 8/18/2003 165.44 GS MBOAEE-3 EN4590 TX 4/30/2004 544.1 WB MBOQBL-1 EN1155 IL 5/21/2004 503.85 IF MBOCA1-1 in this example, all rows would be split to their own spreadsheet. If there was more than one account with the same code in F, they would go to the same spreadsheet. I do need to hard code the various codes into the Macro, as the spreadsheets will be saved with names relating to those codes, so I need a way to prevent errors if a certain code does not exist. Please help! Thank you! -- dfeld71 ------------------------------------------------------------------------ dfeld71's Profile: http://www.excelforum.com/member.php...o&userid=21452 View this thread: http://www.excelforum.com/showthread...hreadid=377809 |
#3
|
|||
|
|||
It's not the whole solution (maybe not even part of the solution?), but if you start with an advanced filter, you can specify which value for column F you want transferred to another sheet, then it will move all records that have that value in F to another sheet in the workbook. Then you move the new sheet to a new book and save that book. All of this can be done in VBA, I just haven't taken the time to write the code. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=377809 |
#4
|
|||
|
|||
I was actually just trying that, and was about to post another question about auto-filters. Here is my code. The problem is, that when the macro is deleting the data from the main tab, after it copies and pastes the necessary info, it is deleting the Header as well. Could someone please tell me how to stop it from doing this? Thank you. asn = ActiveSheet.Name With Application .ScreenUpdating = False .DisplayAlerts = False On Error Resume Next Sheets("MBOEAS-3").Delete Err.Clear Sheets.Add.Name = "MBOEAS-3" With Sheets(asn) .AutoFilterMode = False Set RangeClient = _ Range(.Cells(1, 9), .Cells(.Cells(Rows.Count, 9).End(xlUp).Row, 9)) RangeClient.AutoFilter Field:=1, Criteria1:="=MBOEAS-3" RangeClient.EntireRow.SpecialCells(12).Copy Range("a1") On Error Resume Next With RangeClient .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete End With -- dfeld71 ------------------------------------------------------------------------ dfeld71's Profile: http://www.excelforum.com/member.php...o&userid=21452 View this thread: http://www.excelforum.com/showthread...hreadid=377809 |
#5
|
|||
|
|||
One possible approach:
1) Make a pivot table out of your data. Use Code as a Page field, all others as Row Fields. 2) On the pivot Table Toolbar: Show Pages. Excel will create a separate sheet for each value in the Page field (i.e., Code). The sheets will be given the same name as their respective Codes. HTH, -- George Nicholson Remove 'Junk' from return address. "dfeld71" wrote in message ... I think I posted this in the wrong forum, so I am re-posting it here. Any help would be greatly appreciated. Thank you! Hello everyone. I need some help, and always seem to find my answers here! I have a spreadsheet with a bunch of different accounts on it. Column F contains a code which determines what group the account belongs in. I need to split the data into separate spreadsheets based on this code. in addition, every code will not be there everytime. How do I write a macro to evaluate column F, and take all accounts with certain codes and move them to a new sheet. Also, how do I stop the macro from erroring out, if one of the codes does not exist? Here is a sample of my spreadsheet: A B C D E F EN4590 TX 4/30/2004 544.1 DD MBOEAS-3 EN1155 IL 5/21/2004 503.85 ER MBOAEA-3 DT8455 IL 8/18/2003 165.44 GS MBOAEE-3 EN4590 TX 4/30/2004 544.1 WB MBOQBL-1 EN1155 IL 5/21/2004 503.85 IF MBOCA1-1 in this example, all rows would be split to their own spreadsheet. If there was more than one account with the same code in F, they would go to the same spreadsheet. I do need to hard code the various codes into the Macro, as the spreadsheets will be saved with names relating to those codes, so I need a way to prevent errors if a certain code does not exist. Please help! Thank you! -- dfeld71 ------------------------------------------------------------------------ dfeld71's Profile: http://www.excelforum.com/member.php...o&userid=21452 View this thread: http://www.excelforum.com/showthread...hreadid=377809 |
#6
|
|||
|
|||
Because your ultimately trying to copy these to another workbook, I would use advanced filter rather than auto filter. Advanced filter can filter a list to another location, rather than being limited to filtering a list in place. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=377809 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking References from Multiple Sheets to One Summary Sheet | Setting up and Configuration of Excel | |||
saving excel with multiple sheets, but won't save to proper sheet | Excel Discussion (Misc queries) | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Linking sheets to a summary sheet in workbook | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |