Exporting subset of rows to multiple files
 

Hi,

I have a spreadsheet that contains report information for many clients
Unfortunately the same spreadsheet cannot be sent to all clients and
therefore have to export a subset of rows relating to each client an
then send each file separately. I do this using AutoFilter and nam
the file I create after a column name called Client Code, a simple tex
based field if no more than 8 characters.

Is this possible using VBA, if so how.

A basic set of column names would be Client Name, Client Code, Re
Number, Details.

Any help or guidance will be very much appreciated. Please let me kno
if you require any more info.


Thanks,
Da

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Exporting subset of rows to multiple files
 

Dan,

Make up a named range "List" that includes all the names that you
need. Then you can filter your range (in this example, A1:D1000)
based on the values in "List" and create new workbooks for each of
them. In the macro below, change the directory from C:\Excel in the
SaveAs line, and you're done.

HTH,
Bernie
MS Excel MVP

Sub ExportFilteredData()

Dim myCell As Range
Dim mySht As Worksheet
Dim myBook As Workbook
Dim sourceSht As Worksheet

Set sourceSht = ActiveSheet
Set myBook = ActiveWorkbook

For Each myCell In Range("List")
Set mySht = Sheets.Add(Type:="Worksheet")
With sourceSht.Range("A1:D1000")
.AutoFilter Field:=1, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
End With
mySht.Move
ActiveWorkbook.SaveAs "C:\Excel\" & myCell.Value & ".xls"
ActiveWorkbook.Close
myBook.Activate
Next myCell
End Sub



"dororke" wrote in message
...

Hi,

I have a spreadsheet that contains report information for many
clients.
Unfortunately the same spreadsheet cannot be sent to all clients and
I
therefore have to export a subset of rows relating to each client
and
then send each file separately. I do this using AutoFilter and name
the file I create after a column name called Client Code, a simple
text
based field if no more than 8 characters.

Is this possible using VBA, if so how.

A basic set of column names would be Client Name, Client Code, Ref
Number, Details.

Any help or guidance will be very much appreciated. Please let me
know
if you require any more info.


Thanks,
Dan


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Exporting subset of rows to multiple files
 

Bernie,

Excellent - thank you very much.

Dan


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements