Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating one large workbook into several smaller workbooks
I have one large Excel workbook that I need to break apart and save as
several separate workbooks. The separation point is a column value rather than a specific cell. For example, I have several rows where Column A='00001'. I need a macro that says to delete all rows where Column A<'00001' and save the file with a different name. Then I need to open the larger file again and do the same thing for rows where Column A<'00002', and so on several more times. Does anyone know how to write a code in Visual Basic that would do this? Any suggestions would be extremely helpful. Mary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating one large workbook into several smaller workbooks
Mary, the following is untested, but should work:
sub DeleteRows() dim R as long, lastR as long lastR = cells(65536,1).end(xlup).row for R = lastR to 1 step -1 if cells(R,1).value = "00001" then rows(r).delete next End sub Run the above and then save the file. To delete rows based on a different value, just edit the If test Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "MaryB@FIC" wrote in message ... I have one large Excel workbook that I need to break apart and save as several separate workbooks. The separation point is a column value rather than a specific cell. For example, I have several rows where Column A='00001'. I need a macro that says to delete all rows where Column A<'00001' and save the file with a different name. Then I need to open the larger file again and do the same thing for rows where Column A<'00002', and so on several more times. Does anyone know how to write a code in Visual Basic that would do this? Any suggestions would be extremely helpful. Mary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating one large workbook into several smaller workbooks
Bob, it looks we're almost there, but it's deleting just my header rows (Rows
1-7) and stopping at the first cell that equals "00001". The only change I made to your code was if cells(R,1).value<"00001" because I need it to delete all but the value I put in the if statement. Can I get it to start looking at Row 8, and how do I get it to look past the cells with value "00001" and delete the rest of the rows? Thank you so much for your help. Mary "Bob Flanagan" wrote: Mary, the following is untested, but should work: sub DeleteRows() dim R as long, lastR as long lastR = cells(65536,1).end(xlup).row for R = lastR to 1 step -1 if cells(R,1).value = "00001" then rows(r).delete next End sub Run the above and then save the file. To delete rows based on a different value, just edit the If test Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "MaryB@FIC" wrote in message ... I have one large Excel workbook that I need to break apart and save as several separate workbooks. The separation point is a column value rather than a specific cell. For example, I have several rows where Column A='00001'. I need a macro that says to delete all rows where Column A<'00001' and save the file with a different name. Then I need to open the larger file again and do the same thing for rows where Column A<'00002', and so on several more times. Does anyone know how to write a code in Visual Basic that would do this? Any suggestions would be extremely helpful. Mary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to split a large excel file into multiple smaller exel files. | Excel Discussion (Misc queries) | |||
How to make workbooks smaller in window | Excel Discussion (Misc queries) | |||
Separating 1 workbook into multiple workbooks | Excel Discussion (Misc queries) | |||
divide a large group of people into smaller groups by their vote | Excel Discussion (Misc queries) | |||
Split large sheet into several smaller sheets for emailing | Excel Programming |