Dividing worksheet into multiple workbooks

I have a worksheet in excel that has financial data for several departments and schools. The data is across 2 columns and 948 rows. I need to break the data down into multiple workbooks so I can use and existing email macro to send them to managers.

There is a unique character that separates the data and I was able to use a previously written macro to enter page breaks which is below, but I'm having trouble using the same unique character (or page break) to put the data into separate workbooks. Any ideas?

Dim PBRange As Range, PB As Range
With ActiveSheet
Set PBRange = .Range("A1:A1000")
For Each PB In PBRange
If PB.Value = "***********" Then
.HPageBreaks.Add befo=PB
End If
Next
End With

Take a look at the Data Extraction Assistant. It is designed to create
multiple workbooks from one worksheet.

http://www.add-ins.com/data_extraction_assistant.htm

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel



On May 31, 4:04*pm, MadMak0203 >
wrote:
> I have a worksheet in excel that has financial data for several
> departments and schools. *The data is across 2 columns and 948 rows. *I
> need to break the data down into multiple workbooks so I can use and
> existing email macro to send them to managers.
>
> There is a unique character that separates the data and I was able to
> use a previously written macro to enter page breaks which is below, but
> I'm having trouble using the same unique character (or page break) to
> put the data into separate workbooks. *Any ideas?
>
> Dim PBRange As Range, PB As Range
> With ActiveSheet
> Set PBRange = .Range("A1:A1000")
> For Each PB In PBRange
> If PB.Value = "***********" Then
> HPageBreaks.Add befo=PB
> End If
> Next
> End With
>
> --
> MadMak0203

On Thursday, May 31, 2012 3:04:28 PM UTC-5, MadMak0203 wrote:
> I have a worksheet in excel that has financial data for several
> departments and schools. The data is across 2 columns and 948 rows. I
> need to break the data down into multiple workbooks so I can use and
> existing email macro to send them to managers.
>
> There is a unique character that separates the data and I was able to
> use a previously written macro to enter page breaks which is below, but
> I'm having trouble using the same unique character (or page break) to
> put the data into separate workbooks. Any ideas?
>
> Dim PBRange As Range, PB As Range
> With ActiveSheet
> Set PBRange = .Range("A1:A1000")
> For Each PB In PBRange
> If PB.Value = "***********" Then
> HPageBreaks.Add befo=PB
> End If
> Next
> End With
>
>
>
>
> --
> MadMak0203

Instead why not use a macro to copy the worksheet to a new book>email active sheet,ie:

for each ws in worksheets
ws.copy
activeworkbook.email
ws.close
next ws

It happens that Don Guillett formulated :

I think you really meant...
> for each ws in worksheets
> ws.copy
> activeworkbook.email

activeworkbook.close

> next ws

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion