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

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

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