Separating one large workbook into several smaller workbooks

[Guest]

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

 

[Bob Flanagan]

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

 

[Guest]

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