Exporting subset of rows to multiple files

  • Thread starter Thread starter dororke
  • Start date Start date
D

dororke

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
 
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 said:
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


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

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